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