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
- Input_string: The string which will be replicated the number of times. It can be a string, character, or any number.
- 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
Lower() Function Example In MySQL and SQL Server