SQL Replicate Function Example | Replicate() In SQL

SQL REPLICATE() is an inbuilt function that is used for repeating the string for the specified number of times. The replicate() function is one of the SQL String Functions, which is used to repeat the existing string for a given number of times. This SQL replicate function accepts both character string or binary data and returns the same type as the specified expression.

SQL Replicate Function

The REPLICATE() function repeats the string a specified number of times. See the following syntax.

SELECT REPLICATE (input_string, count);

Parameters

1. Input_string: The string which will be replicated the number of times. It can be a string, character, or any number.
2. Count: This is the number that will specify the number of times string will be repeated.

Example of Replicate Function

Query 1

SELECT REPLICATE (‘x’, 5);

Output

xxxxx

Here string is x and counts 5. So, x was repeated 5 times.

Let’s apply a REPLICATE function on the Table.

TABLE: (EMPLOYEE)

Emp_id	Emp_name	City	State	Salary
101	Rohit	Patna	Bihar	30000
2010	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	West Bengal	60000
5010	Akash	Vizag	Andhra Pradesh	70000

 

In the above table, Emp_id should have 5 characters. As it does not have 5 characters, our task is to append 0 on Emp_id to make its length 5.

To do this, the following query has to be considered.

Query

SELECT CONCAT (REPLICATE (‘0’, 5-LEN (Emp_id)), Emp_id) AS Emp_id, Emp_name, City, State, Salary 
FROM EMPLOYEE;

Output

Emp_id	Emp_name	City	State	Salary
00101	Rohit	Patna	Bihar	30000
02010	Shivam	Jalandhar	Punjab	20000
00301	Karan	Allahabad	Uttar Pradesh	40000
00401	Suraj	Kolkata	West Bengal	60000
05010	Akash	Vizag	Andhra Pradesh	70000

 

Explanation

First, you have to use the LEN() function to calculate the number of zeros to be padded:

5-LEN (Emp_id)

Second, you have to use the REPLICATE () function to replicate several zeros to be padded:

REPLICATE (‘0’, 5-LEN (Emp_id))

Third, you have to use the CONCAT() function to concatenate the result returned by the replicate function with Emp_id.

CONCAT (REPLICATE (‘0’, 5-LEN (Emp_id)), Emp_id)

The function works with SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse.

#SQL Replicate Function in SQL Server

The REPLICATE Function in Sql Server repeats the original string with a specified number of times.  See the following syntax.

SELECT REPLICATE(Emp_name, 2)
FROM Employee;

Finally, SQL Replicate() function tutorial is over.

Recommended Posts

SQL LTRIM Function Example

Lower() Function Example In MySQL and SQL Server

LEFT() In SQL Server And MySQL

SQL DIFFERENCE Function

CONCAT_WS Method in SQL