Tech: SQL String Concatenation

Tech: SQL String Concatenation

This will be a very short basic article dealing with very basic string concatenation.

In this instance I was asked to combine a first name and last name in a table.  First names and last names are separate columns in the SQL database I happened to be working with.  For full MSDN reference you can go to this link.

First

Here is a simple SQL statement, I am retrieving the top 10 first and last names 

select top 10 first_name, last_name
from ccontacts

This script will return the first 10 first and last names from the table named ccontacts.

You can see that the results contain a first name and last name.  Now I need the first name and last name returned in one column separated by a space, similar to the way you would see them in regular print.

To return results like this we need to use a simple concatenation statement within the select statement.

In order to return the first name and last name together as a full name field as you can see below, adding the first name, a blank space ' ', and the last name will return both first and last name as a full name field.  I used the 'as' modifier to allow the column name to be fullname.

select top 10 first_name, last_name, ( first_name  + ' ' +  last_name ) as fullname from ccontacts

Here you can see the results with firstname_name, last_name, and fullname.

Now lets say that we want the first name and last name but we want the format to be last name then a comma, space, and finally first name.

select top 10 first_name, last_name, ( last_name  + ', ' +  first_name ) as fullname from ccontacts

As you can see below we are still returning the first name, last name, but now the fullname column is last name, comma, space, first name.

As I mentioned at the onset of this article, it would be very simple, dealing with simple string concatenation.  While working my "day job" as I run into small tech issues I will continue to create articles such as these to outline tech issues in general.

have a great day!

~Yati