Concatenate Two Strings
This statement concatenates the Home_State and Home_City columns to create a location value. The concatenation is shown twice, using the CONCAT function and the concatenate operator.
SELECT TOP 5
{fn CONCAT({fn CONCAT(HomeCity,', ')}, HomeState)} AS LocationWithConcatFunction,
HomeCity||', '||HomeState AS LocationWithConcatOperator
FROM Sample.Person
LocationWithConcatFunction |
LocationWithConcatOperator |
Denver, CO |
Denver, CO |
Boston, MA |
Boston, MA |
Albuquerque, NM |
Albuquerque, NM |
Jacksonville, FL |
Jacksonville, FL |
Lexington, KY |
Lexington, KY |
This statement concatenates a string and a NULL, which returns a column of NULLs.
SELECT {fn CONCAT(HomeState,NULL)} AS StrNull
FROM Sample.Person
StrNull |
NULL |
NULL |
NULL |
NULL |
NULL |
This statement shows that numbers are converted to canonical form before concatenation. To avoid this, you can specify the number as a string, as shown in the second part of this statement.
SELECT TOP 5
{fn CONCAT(HomeState,0012.00E2)} AS StrNum,
{fn CONCAT(HomeState,'0012.00E2')} AS StrStrNum
FROM Sample.Person
StrNum |
StrStrNum |
CO1200 |
CO0012.00E2 |
MA1200 |
MA0012.00E2 |
NM1200 |
NM0012.00E2 |
FL1200 |
FL0012.00E2 |
KY1200 |
KY0012.00E2 |
The statement shows that trailing blank spaces are retained. When you concatenate a two-letter state field with 10 spaces, the length of each value in the concatenated column is 12.
SELECT TOP 5
HomeState,
CHAR_LENGTH({fn CONCAT(HomeState,' ')}) AS StrSpace
FROM Sample.Person2
HomeState |
StrSpace |
CO |
12 |
MA |
12 |
NM |
12 |
FL |
12 |
KY |
12 |