SQL Server REPLICATE Function

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

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);Copy

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;Copy

Here is the output:

result
------
zzz

(1 row affected)Copy

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
);Copy

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');Copy

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;Copy

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)Copy

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

REPLICATE('0', 10 - LEN(part_no)Copy

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

CONCAT(
    REPLICATE('0', 10 - LEN(part_no)), 
    part_no
) part_noCopy

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