SQL Server String REPLICATE() Function

SQL Server REPLICATE() Function

The SQL Server REPLICATE() function is used to repeat a specified string a given number of times. This function is useful for generating repeated patterns or padding strings in SQL queries.

Syntax

SELECT REPLICATE(string, number_of_times);

The REPLICATE() function takes two arguments:

  • string: The string to be repeated.
  • number_of_times: The number of times to repeat the string.

Example SQL Server REPLICATE() Function Queries

Let's look at some examples of SQL Server REPLICATE() function queries:

1. Basic REPLICATE() Example

SELECT REPLICATE('SQL', 3) AS result;

This query repeats the string 'SQL' three times. The result will be:

result
---------
SQLSQLSQL

2. REPLICATE() with a Column

SELECT first_name, REPLICATE(first_name, 2) AS repeated_name
FROM employees;

This query repeats the first_name column twice for each employee. The result will show the first_name and the repeated version as repeated_name.

3. REPLICATE() with a Variable

DECLARE @myString VARCHAR(50);
SET @myString = 'SQL Server';
SELECT REPLICATE(@myString, 2) AS result;

This query uses a variable to store a string and then repeats it twice. The result will be:

result
------------------
SQL ServerSQL Server

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the REPLICATE() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    description VARCHAR(50)
);

In this example, we create a table named example_table with columns for id and description.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, description) VALUES (1, 'Apple');
INSERT INTO example_table (id, description) VALUES (2, 'Banana');
INSERT INTO example_table (id, description) VALUES (3, 'Cherry');

Here, we insert data into the example_table.

Step 3: Using the REPLICATE() Function

This step involves using the REPLICATE() function to repeat the description column.

SELECT id, description, REPLICATE(description, 3) AS repeated_description
FROM example_table;

This query retrieves the id, description, and the repeated version of the description column for each row in the example_table. The result will be:

id  description  repeated_description
--- ------------ ---------------------
1   Apple        AppleAppleApple
2   Banana       BananaBananaBanana
3   Cherry       CherryCherryCherry

Conclusion

The SQL Server REPLICATE() function is a powerful tool for repeating a specified string a given number of times. Understanding how to use the REPLICATE() function and its syntax is essential for effective string manipulation and data processing in SQL Server.