Extract Fields

06 Jun 2022 38964 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:

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!

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:

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

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

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

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.

Report a Bug

In this article