29 Sep 2019 0 minutes to read Contributors
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:
you don't know where the last name begins, but you do know that it is the last one in the field.
This is how you want to extract the information:
12345678910111213 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)
Use the REVERSE() function (available since SQL Server 2008). It does what it implies!
123456789101112 CREATE FUNCTION dbo.fn_extractname( @string NVARCHAR(MAX), @last BIT = 0 )RETURNS NVARCHAR(MAX)ASBEGINDECLARE @returnstring NVARCHAR(MAX) = REVERSE( @string )DECLARE @charindex INT = CHARINDEX( ' ', @returnstring, 1 )IF( @charindex = 0 )RETURN @stringRETURN 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:
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.
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.
12 IF( @charindex = 0 )RETURN @string
It can be just 1 word or no space was found, then return the original string.
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.
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.