String Concat

Using Functions and Expressions

  1.Concatenating Strings

String can be concatenated with a operator (+ plus), the generic syntax is 

The syntax is 

  <string or column name> + <string or column name>.

remember, in case you are trying to add space then you can use (' ') , instead of space if you pass null then this results as null. Example describes below.

Example 1.

 select 'text1'+'text2'

 

Example 2.

 SELECT country, FirstName + ' ' + LastName AS "Full Name"

 FROM HR.Employees;

Example 3.

 SELECT country, LastName + ', ' + FirstName AS "Full Name"

 FROM HR.Employees;

Query 1 shows that you can concatenate two strings. Queries 2 and 3 demonstrate concatenating the LastName and FirstName columns along with either a space or a comma and space. Notice that you specified the alias, Full Name, to provide a column header for the result of the expressions combining FirstName and LastName. If you did not provide the alias, the column header would be (No column name), as in query 1.   

Concatenating Strings and NULL

When we concatenating a string with a NULL, NULL is returned. 

Example 4.

 SELECT country, firstname+''+middleName + ' ' + lastname AS "Full Name"

 FROM HR.Employees;

Example 5.

SELECT country, firstname+''+null + ' ' + lastname AS "Full Name"

 FROM HR.Employees;

In case any null column value is added with any string result appear as null.

Concat

In SQL 2012, concat introduced, you can pass any number of values inside concat using separator (,). You can pass column name or string or text or even you can pass variable for combining string. if you will try concat prior SQL 2012 version you will get error 

Msg 195, Level 15, State 10, Line 1

'concat' is not a recognized built-in function name.

Example:-

-- Simple CONCAT statement

SELECT CONCAT ('I ', 'love', ' writing', ' T-SQL') AS RESULT;

--Using variable with CONCAT

DECLARE @a VARCHAR(30) = 'My birthday is on '

DECLARE @b DATE = '01/01/1991'

SELECT CONCAT (@a, @b) AS RESULT;

--Using CONCAT with table rows

USE AdventureWorks2012

SELECT CONCAT (AddressLine1, PostalCode) AS Address

FROM Person.Address;