ayesamson

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

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

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

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

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

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

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