Sunday, September 9, 2012

Tech: SQL String Concatenation

sql_server

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.

resluts_no_concat

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.

slq_concat_fname_lname

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.


slq_concat_lname_comma_fname

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   


5 comments:

  1. Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too. There tend to be not many people who can certainly write not so simple posts that artistically. Continue the nice writing TutuApp iOS 11

    ReplyDelete
  2. This article gives the light in which we can observe the reality. This is very nice one and gives indepth information. Thanks for this nice article. Download iOS 11.3

    ReplyDelete
  3. I stumbled upon this blog with a host of new possibilities to explore. Thank you for the friendly in sharing. TutuApp Download

    ReplyDelete