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 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.

Usually, you may have come across with a hint pattern most of the time during a password recovery OTP or any security page on bank websites wherein users are asked to choose either phone number or mail id for receiving an OTP while few of characters of phone number or mail id are hidden on page, especially characters are replaced with a  ‘*’ ,  which may be something like “Your OTP for password recovery is sent on this mail xyz*****@gmail.com”.

Obviously, it is done for security reasons,  just to give a quickly hint to user that you have to check that specific mail id or phone number on which OTP is being sent without showing a complete mail Id or phone number details on screen.

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 displaying complete 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()

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 633 total views,  5 views today