MayfieldGlobal

Concatenation (joining 2 or more characters or strings of the same data type) is accomplished using the “+” operator. For example, let’s say that I want to join the strings “Able” and “Baker”. To do so, I would simply write:

SELECT 'Windows' + 'Vista' AS OSName

Running this query would return:

OSName
------------
WindowsVista

OK so far, but what if you want a space between “Windows” and “Vista”? That could be accomplished in 2 ways: Either by including it in one of our existing strings (i.e. “Windows ” or ” Vista”), as in:

SELECT 'Windows ' + 'Vista' AS OSName

or by adding an additional concatenation operator:

SELECT 'Windows' + ' ' + 'Vista' AS OSName

Both methods return the desire result:

OSName
-------------
Windows Vista

A more useful example that also demonstrates the “of the same data type” caveat mentioned above:

SELECT 'Today is: ' + CONVERT(varchar(10), GETDATE(), 101) AS Today

would return:

Today
--------------------
Today is: 01/05/2008

However, if we failed to convert result of GETDATE() into a varchar, we would receive an error similar to:

Today
----------------------
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

For additional information, please visit: http://msdn2.microsoft.com/en-us/library/ms177561.aspx