Sometimes You Need to Push Strings Together

Every now and again, you’re probably going to have to slap a few strings together in your fun with SQL. A long time ago (20 years ago), in SQL Server world, there was only one way to do it – and sadly, you’ll probably see many developers still doing it this way.

SELECT LastName + ', ' + FirstName
FROM dbo.Person1

Yes, that’s right. Just use the + operator between every string/field that you want to slap together. Now, you should be sure to make sure that your data is as clean as possible before displaying it to your clients. For example, I would at least do the following in most cases:

SELECT TRIM(LastName) + ', ' + TRIM(FirstName) 
FROM dbo.Person1

This way you won’t have multiple spaces between the LastName and the comma.

But there is a potential problem.

If any of the fields have a value of NULL, then your answer will be NULL period. As we all know, if a client sees NULL, they will file a defect in any way they can quite quickly.

That takes us to our next way of pushing strings together.

SELECT CONCAT(TRIM(LastName), ', ', TRIM(FirstName))
FROM dbo.Person1

Now, the CONCAT function will only work in SQL Server 2012 and above.

Also, the CONCAT function does something for us that the + operator doesn’t. Do you remember that pesky old NULL functionality that would make everything show as a NULL if one of the fields was a NULL?

Well, the CONCAT function stops that from being a thing. For example, let’s run this bad boy:

;WITH PeopleData AS 
	(SELECT NULL AS LastName, 'HelpMe' AS FirstName)
SELECT CONCAT(TRIM(LastName), ', ', TRIM(FirstName))
FROM PeopleData

This shows the result of “, HelpMe” unlike the NULL that the + operator only displays.

And with that, we have come to the end of yet another fascinating blog post. So until next time…