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)

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

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