AppDividend
Latest Code Tutorials

SQL Replicate Function Example Tutorial

0

In this topic, we will talk about SQL Replicate Function Example. SQL REPLICATE function 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 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.