SQL Server REPLICATE Function

Summary: in this tutorial, you will learn how to use the SQL Server REPLICATE() function to replicate a string a specified number of times.

SQL Server REPLICATE() function overview

The REPLICATE() function repeats a string a specified number of times. Its syntax is straightforward as follows:

REPLICATE(input_string, count);
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• input_string is an expression that evaluates to a value of the character or binary type.
• count is the number of times that the input_string will be repeated in the result string.

SQL Server REPLICATE() function examples

Let’s take some examples of using the REPLICATE()function.

A) Using REPLICATE() function to repeat a literal string

This example uses the REPLICATE() function to repeat the character z three times:

SELECT
    REPLICATE('z',3) result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
------
zzz

(1 row affected)

B) Using REPLICATE() function making columns with leading zero data

First, create a new table named spare_parts in the production schema:

CREATE TABLE production.spare_parts (
    part_id INT IDENTITY PRIMARY KEY,
    part_no VARCHAR(10) NOT NULL UNIQUE,
    description VARCHAR(50) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Suppose the part_no must always have 10 characters. If a spare part has less than 10 characters, the application needs to pad leading zeros so that the length of the spare part always has 10 characters.

Second, insert some values into the production.spare_parts table:

INSERT INTO 
    production.spare_parts(part_no, description)
VALUES
    ('FRMTUBE','Frame Tube'),
    ('CHNCO','Chain Cover'),
    ('CRKS','Cranks');
Code language: SQL (Structured Query Language) (sql)

Third, select data from the production.spare_parts table, left pads zeros using the REPLICATE() function:

SELECT 
    part_id,
    CONCAT(
        REPLICATE('0', 10 - LEN(part_no)), 
        part_no
    ) part_no,
    description
FROM 
    production.spare_parts;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example:

First, use the LEN() function to calculate the number of zeros to be padded:

10 - LEN(part_no)
Code language: SQL (Structured Query Language) (sql)

Second, use the REPLICATE() function to replicate the necessary zeros to be padded:

REPLICATE('0', 10 - LEN(part_no)
Code language: SQL (Structured Query Language) (sql)

Third, use the CONCAT() function to left pad the zeros to the part no:

CONCAT(
    REPLICATE('0', 10 - LEN(part_no)), 
    part_no
) part_no
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server REPLICATE() function to replicate a string a specified number of times.