AppDividend
Latest Code Tutorials

SQL Replicate Function Example | Replicate() In SQL

0

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_idEmp_nameCityStateSalary
101RohitPatnaBihar30000
2010ShivamJalandharPunjab20000
301KaranAllahabadUttar Pradesh40000
401SurajKolkataWest Bengal60000
5010AkashVizagAndhra Pradesh70000

 

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_idEmp_nameCityStateSalary
00101RohitPatnaBihar30000
02010ShivamJalandharPunjab20000
00301KaranAllahabadUttar Pradesh40000
00401SurajKolkataWest Bengal60000
05010AkashVizagAndhra Pradesh70000

 

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

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.