REPLICATE() Function in SQL Server

SQL Server REPLICATE() function is used to repeat a specified string for a given number of time.

Syntax:

REPLICATE (string, integer)

Example:

1. 

Select replicate('A',5)

Output:

AAAAA

2.

Select replicate(('A'+' '),5)

Output:

A A A A A 

3.

select Name,REPLICATE('0',3)+ProductLine as Prod_Code from Production.Product

where ProductLine='R'

'CONCAT' is not a recognized built-in function name in SQL Server 2008

 CONCAT function newly introduced from SQL Server 2012.

SQL Server 2008 will throw an error 

'CONCAT' is not a recognized built-in function name

So in SQL Server 2008 we can use "+" operator to concat the 2 or more strings. It is always suggested to use cast your columns before using them. This operator will throw an error if the first operand is a number since it thinks will be adding and not concatenating

cast('data1' as varchar) + cast('data2' as varchar) + cast('data3' as varchar)

There are few differences between "CONCAT" and "+" operator

SELECT 'A' + 'B' + 'C' 
SELECT CONCAT('A', 'B', 'C') 
SELECT 'A' + 'B' + NULL 
SELECT CONCAT('A', 'B', NULL)

output will be ABC,ABC,NULL,AB respectively.