REPLICATE FUNCTION IN SQL SERVER 2005

REPLICATE function repeats a given string specified number of times.

The syntax : REPLICATE ( string_expression ,integer_expression )

Eg.

1. SELECT REPLICATE (‘100’ , 5 )

OUTPUT
__________________

100100100100100

2. SELECT REPLICATE (100 , 5 ) — It is taking integer value also.

OUTPUT
__________________

100100100100100

3. SELECT REPLICATE (100 , -1 ) — It will return null if the integer value is negative

OUTPUT
_________________

NULL

4. SELECT REPLICATE (100 , 0 ) — It will return blank

5. SELECT REPLICATE (100 , ‘one’ ) — It will give you syntax error

OUTPUT
_________________
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘one’ to data type int.

REPLICATE function can be used to combine it with other string
functions and replace leading or trailing spaces with another character.

In the below example we are replacing spaces with character 1

DECLARE @StringInput VARCHAR(40)
SELECT @StringInput= ‘         PAWAN KUMAR’
SELECT @StringInput =  REPLICATE(‘1’, LEN(@StringInput) – LEN(LTRIM(@StringInput)))  + LTRIM(@StringInput)
SELECT @StringInput

Pawan Kumar

Pawankkmr@hotmail.com