Extract Fields

06 Jun 2022 38912 views 0 minutes to read Contributors

Situation

You have a field that contains a display name (combination of last name and first name): And you would like to have the result like this:

Problem

you don't know where the last name begins, but you do know that it is the last one in the field.

Solution

This is how you want to extract the information:

		
			
T-SQL
			
Transact-SQL
			

			
DECLARE @contacts TABLE( displayname NVARCHAR(512), firstname NVARCHAR(256) NULL, lastname nvarchar(256) NULL)
INSERT INTO @contacts( displayname )
VALUES 
('Michael Jack Abbey'),
('John Accrington'),
('Bob Dillan Addison'),
('Paul Ashfort Adling'),
('Helen Allsworth'),
('Tim Allsworth'),
('Neil Ambridge'),
('Ian Attwood'),
('Michael R.')
SELECT displayname, dbo.fn_extractname(displayname, 0 ) as firstname, dbo.fn_extractname(displayname, 1 ) as lastname from @contacts
			

				

					

				

					
1
2
3
4
5
6
7
8
9
10
11
12
13
				
						
DECLARE @contacts TABLE( displayname NVARCHAR(512), firstname NVARCHAR(256) NULL, lastname nvarchar(256) NULL)
INSERT INTO @contacts( displayname )
VALUES 
('Michael Jack Abbey'),
('John Accrington'),
('Bob Dillan Addison'),
('Paul Ashfort Adling'),
('Helen Allsworth'),
('Tim Allsworth'),
('Neil Ambridge'),
('Ian Attwood'),
('Michael R.')
SELECT displayname, dbo.fn_extractname(displayname, 0 ) as firstname, dbo.fn_extractname(displayname, 1 ) as lastname from @contacts
					
				
			
		

dbo.fn_extractname(displayname, 0 ) is a function (see code below). first parameter is the string containing the contact information, second parameter defines which part you want to extract (0=first name(s) / 1=last name)

REVERSE

Use the REVERSE() function (available since SQL Server 2008). It does what it implies!

		
			
T-SQL
			
Transact-SQL
			

			
CREATE FUNCTION dbo.fn_extractname( @string NVARCHAR(MAX), @last BIT = 0 )
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @returnstring NVARCHAR(MAX) = REVERSE( @string )
	DECLARE @charindex INT = CHARINDEX( ' ', @returnstring, 1 )

	IF( @charindex = 0 )
		RETURN @string
	
	RETURN REVERSE( SUBSTRING( @returnstring, CASE WHEN @last = 1 THEN 1 ELSE @charindex+1 END, CASE WHEN @last = 1 THEN @charindex-1 ELSE LEN(@string)-1 END))
END
			

				

					

				

					
1
2
3
4
5
6
7
8
9
10
11
12
				
						
CREATE FUNCTION dbo.fn_extractname( @string NVARCHAR(MAX), @last BIT = 0 )
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @returnstring NVARCHAR(MAX) = REVERSE( @string )
	DECLARE @charindex INT = CHARINDEX( ' ', @returnstring, 1 )
 
	IF( @charindex = 0 )
		RETURN @string
	
	RETURN REVERSE( SUBSTRING( @returnstring, CASE WHEN @last = 1 THEN 1 ELSE @charindex+1 END, CASE WHEN @last = 1 THEN @charindex-1 ELSE LEN(@string)-1 END))
END
					
				
			
		

Here is how a reverse string looks like:  It doesn't look readable, but it does not have to be. We will reverse it back to normal once we're done. Some explanations:

		
			
T-SQL
			
Transact-SQL
			

			
	DECLARE @returnstring NVARCHAR(MAX) = REVERSE( @string )
			

				

					

				

					
1
				
						
	DECLARE @returnstring NVARCHAR(MAX) = REVERSE( @string )
					
				
			
		

Always reverse the string because the last name is often only 1 combination and first names can be many.

		
			
T-SQL
			
Transact-SQL
			

			
	DECLARE @charindex INT = CHARINDEX( ' ', @returnstring, 1 )
			

				

					

				

					
1
				
						
	DECLARE @charindex INT = CHARINDEX( ' ', @returnstring, 1 )
					
				
			
		

Find the first occurence where the last name might end. Second parameter is where to start. First character always start at position 1.

		
			
T-SQL
			
Transact-SQL
			

			
	IF( @charindex = 0 )
		RETURN @string
			

				

					

				

					
1
2
				
						
	IF( @charindex = 0 )
		RETURN @string
					
				
			
		

It can be just 1 word or no space was found, then return the original string.

		
			
T-SQL
			
Transact-SQL
			

			
	RETURN REVERSE( SUBSTRING( @returnstring, CASE WHEN @last = 1 THEN 1 ELSE @charindex+1 END, CASE WHEN @last = 1 THEN @charindex-1 ELSE LEN(@string)-1 END))
			

				

					

				

					
1
				
						
	RETURN REVERSE( SUBSTRING( @returnstring, CASE WHEN @last = 1 THEN 1 ELSE @charindex+1 END, CASE WHEN @last = 1 THEN @charindex-1 ELSE LEN(@string)-1 END))
					
				
			
		

In the end, reverse the string again and return it back.   SUBSTRING has 3 parameters. first parameter is the string you want to process second parameter is where you want to start (first character starts at position 1) third parameter is the length of the string you want to retrieve.   Example: SUBSTRING( 'Michael Jack Abbey', 9, 4 ) will return "Jack"   So for the second parameter (where do we start) it depends on @last = 1. If we want to retrieve the last name then we start at the beginning (position 1) otherwise start at at the 1st found ' ' (space) + 1 (skip the space)   for the third parameter (how much characters we want to return) depends also on @last = 1 if we want to retrieve the last name then we stop at the 1st found ' ' (space) - 1 (skip the space), else we want to have have the remainder (LEN(@string)) of the string. LEN() will return a larger number than there are characters left in the string, but that is not an issue.  

About us

Over the years, SQLTreeo gained precious know-how which we transformed into customer support, managed database services and database solutions. In order to express our knowledge and experiences, we tend to work with anything less than the best-trained staff and that is the key to success and the biggest asset of our company. Thus, all our database management services are controlled by high level skilled and Senior database administrators. We can help you in every aspect of SQL Server.