Concatenation Fields

In some instances you are required to piece together data by slapping fields together. This is probably most common with names, addresses and such. Using the AdventureWorksLT database I will illustrate how to concatenate a few fields to comprise a single full name field. First we need to analyze the data to see what we have.

SELECT FirstName
       ,MiddleName
       ,LastName
       ,Suffix
FROM [AdventureWorksLT].[SalesLT].[Customer]

Results
01.SampleData

As you can see we have rows that contain only a first and last name, some that have a middle initial and some have a suffix. Unfortunately there was not a record that had a first, last and suffix so I modified record ID 12 because I wanted to touch base on all scenarios. I find it helpful.

Based on the data we don’t need to worry about the first and last name fields but we do need to be concerned with the middle initial and suffix since some are populated while others are not. So how are we going to handle this? Well there are a few ways but I will show you how to address this with SELECT CASE.

SELECT Firstname + space(1) +
       CASE
          WHEN MiddleName IS NULL THEN LastName
          ELSE MiddleName + space(1) + LastName
       END +
       CASE
          WHEN Suffix IS NOT NULL THEN space(1) + Suffix
          ELSE space(0)
       END 'FullName'
FROM [AdventureWorksLT].[SalesLT].[Customer]

Well let’s review before we move on to the results… line numbers 3 & 4 basically state that when you find a NULL value for the MiddleName just display the LastName instead otherwise display the MiddleName add a space then display the LastName.

Lines 7 & 8 is similar. When the Suffix field contains a value other than NULL add a space and display the Suffix otherwise show nothing.

Results
02.FieldsConcat

Now let’s put everything together to see how it all looks.

SELECT FirstName
       ,[MiddleName]
       ,[LastName]
       ,[Suffix]
       ,FirstName + space(1) +
       CASE
          WHEN MiddleName IS NULL THEN LastName
          ELSE MiddleName + space(1) + LastName
       END +
       CASE
          WHEN Suffix IS NOT NULL THEN space(1) + Suffix
          ELSE space(0)
       END 'FullName'
FROM [AdventureWorksLT].[SalesLT].[Customer]

Note:

The fact this dataset has explicit NULL values as opposed to blank/empty values for the MiddleName and Suffix columns made this easy. If it were the latter of the two the results would differ and require additional measures.

Results
03.ConcatFields

I modified CustomerID 20 by replacing NULL in the MiddleName field with a single space and CustomerID 22 by replacing NULL in the Suffix with a single space. Of course the MiddleName is more apparent as it spaces out the FirstName and LastName more than it should and the Suffix is less obvious but it does add a space after the LastName.

Results
04.NonNULLValues

I was able to address these by adding a two additional WHEN statements to the existing CASE statements.

SELECT FirstName
       ,[MiddleName]
       ,[LastName]
       ,[Suffix]
       ,FirstName + space(1) +
       CASE
          WHEN MiddleName IS NULL THEN LastName
          WHEN MiddleName = space(1) THEN LastName
          ELSE MiddleName + space(1) + LastName
       END +
       CASE
          WHEN Suffix = space(1) THEN space(0)
          WHEN Suffix IS NOT NULL THEN space(1) + Suffix
          ELSE ''
       END 'FullName'
FROM [AdventureWorksLT].[SalesLT].[Customer]

Results
05.NonNULLFixed

Another issue:

What if there were leading or trailing spaces in the FirstName and LastName fields? I modified Record ID 1 and added a 10 space before and after Orlando.

Results
06.Whitespace-FirstnameColumn

Well in this case I would keep the same syntax but would encase the fields within: RTRIM(LTRIM(FirstName)) respectively as it removes the whitespaces before and after.

SELECT FirstName
       ,[MiddleName]
       ,[LastName]
       ,[Suffix]
       ,LTRIM(RTRIM(FirstName)) + space(1) +
       CASE
          WHEN MiddleName IS NULL THEN LastName
          WHEN MiddleName = space(1) THEN LastName
          ELSE MiddleName + space(1) + LastName
       END +
       CASE
          WHEN Suffix = space(1) THEN space(0)
          WHEN Suffix IS NOT NULL THEN space(1) + Suffix
          ELSE ''
       END 'FullName'
FROM [AdventureWorksLT].[SalesLT].[Customer]

Results
07.Trimmed-Firstname

Advertisements

2 thoughts on “Concatenation Fields

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s