SQL REPLICATE()

SQL REPLICATE function repeats a string value a specified number of times.

SYNTAX

REPLICATE (inputstring, integer)

inputstring is the string of a character string or binary data type. It can be either a character or binary data.

integer is an any integer value, including bigint. If it is negative then NULL value is returned.

Lets look at an example of using REPLICATE() function in SQL.

Using Replicate function with string

The following example uses REPLICATE() function and repeats a given string for five time .

DECLARE @inputsrtring VARCHAR(100)='welcome to sqlskull.com ! '

SELECT REPLICATE(@inputsrtring,5) AS String_After_Replicate

Using Replicate function with character

The following example replicates a character for five time .

DECLARE @inputChar VARCHAR(5)= 'S'

SELECT REPLICATE(@inputChar,5) AS After_Replicate

Using a Replicate function for generating a hint pattern (overriding a string characters with any specific character)

You can override a string character with any specific character using replicate() function.

Following statement uses replicate function which adds five * in place of last five characters of phone number.

SELECT  PhoneNumber ,
SUBSTRING (PhoneNumber ,1,(LEN(PhoneNumber)-5 ) ) + REPLICATE('*',5) 
AS PhoneNumber_AfterReplicate  
FROM [Person].[PersonPhone]

 

As you can see, last five characters are replicated with ‘*’,  it gives a hint that password is being sent on that phone number without exposing full phone number.

Here, we have used substring function and len function to get the substring from a string after excluding last five character of its length .

Also Read..

CHARINDEX()

LEN()

DATALENGTH()

SUBSTRING()

REPLACE()

DATEPART()

DATEDIFF()

GETDATE()

CURRENT_TIMESTAMP()

EOMONTH()

DATEADD()

DATEFROMPARTS()

DATENAME()