SQL - REPLICATE() Function

---

---

The SQL REPLICATE() function is an inbuilt function that is used to repeat a string a specified number of times.

It accepts two parameters as str and count type, and returns a repeated String. If the count is less than 1, it returns an empty String, and if the given String or count is NULL, then this function returns a NULL value. We can also use this function with a table to repeat the data of any column.

Note − SQL provides a REPLICATE() function instead of a REPEAT() function, both functions have the same functionality.

Syntax

Following is the syntax of the SQL REPLICATE() function −

REPLICATE(str, count); 

Parameters

• str − It is a string which we want to repeat.
• count − It is an int value that describe that how many times to repeat the string.

Return value

This function returns the repeated value.

Example

In the following example,we are using the REPLICATE() function to repeat the string “HelloSQL” at the specified number 5 times.

SELECT REPLICATE('HelloSQL', 5) AS REPEATE_STRING_5_TIMES;

Output

The above statement produes the following output −

+------------------------------------------+
| REPEATE_STRING_5_TIMES                   |
+------------------------------------------+
| HelloSQLHelloSQLHelloSQLHelloSQLHelloSQL |
+------------------------------------------+

Example

If the count is less than 1, the REPLICATE() function returns an empty string.

In the following example, we are using the String REPLICATE() function to repeat a string ‘ABC’ at the specified number 0 times.

SELECT REPLICATE('HelloSQL', 0) AS REPEATE_STRING_0_TIMES;

Output

The above SQL query produces the following output −

+------------------------+
| REPEATE_STRING_0_TIMES |
+------------------------+
|                        |
+------------------------+

Example

If both the parameters str and count have the NULL value, this method returns NULL.

Following is another example of the REPLICATE() function, where we will pass the NULL value of both the parameters str and count.

SELECT REPLICATE(NULL, NULL) AS REPEATE_STRING_NULL_TIMES;

Output

Following is the output of the above query −

+------------------------------------------------------+
| REPEATE_STRING_NULL_TIMES                            |
+------------------------------------------------------+
| NULL                                                 |
+------------------------------------------------------+

Example

You can also pass the column name of a table as an argument to the REPLICATE() function to repeat the content of the column. Assume we have created a table in SQL with the name Customersusing the CREATE statement as follows −

CREATE TABLE CUSTOMERS(    
   ID INT NOT NULL,    
   NAME VARCHAR (20) NOT NULL,    
   AGE INT NOT NULL,    
   ADDRESS CHAR (25) ,    
   SALARY DECIMAL (18, 2));

Now, let's insert some records in to the Customers table using the INSERT statement as shown below −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

Now, let's use the REPLICATE() function to repeat the data of the column AGE at the specified number 3 times.

Following is the statement to use the REPLICATE() function with the table −

SELECT REPLICATE(AGE, 3) AS REPEATE_AGE_COLUMN_3_TIMES FROM CUSTOMERS;

Output

The above Select statement produces the following output −

+----------------------------+
| REPEATE_AGE_COLUMN_3_TIMES |
+----------------------------+
| 323232                     |
| 252525                     |
| 232323                     |
| 252525                     |
+----------------------------+