Sql server® 2012 t-sql fundamentals

 

CHAPTER 2

 

Single-Table Queries

 

 69

The 

REPLICATE

 Function

The REPLICATE function replicates a string a requested number of times.

Syntax

REPLICATE(string, n)

For example, the following code replicates the string ‘abc’ three times, returning the string 

‘abcabcabc’.

SELECT REPLICATE('abc', 3);

The next example demonstrates the use of the REPLICATE function, along with the RIGHT func-

tion and string concatenation. The following query against the Production.Suppliers table generates a 

10-digit string representation of the supplier ID integer with leading zeros.

SELECT supplierid, 

  RIGHT(REPLICATE('0', 9) + CAST(supplierid AS VARCHAR(10)), 10) AS strsupplierid 

FROM Production.Suppliers;

The expression producing the result column strsupplierid replicates the character 0 nine times 

(producing the string ‘ 000000000’) and concatenates the string representation of the supplier ID to 

form the result. The string representation of the supplier ID integer is produced by the CAST func-

tion, which is used to convert the data type of the input value. Finally, the expression extracts the 10 

rightmost characters of the result string, returning the 10-digit string representation of the supplier ID 

with leading zeros. Here’s the output of this query, shown in abbreviated form.

supplierid  strsupplierid 

----------- ------------- 

29          0000000029 

28          0000000028 

4           0000000004 

21          0000000021 

2           0000000002 

22          0000000022 

14          0000000014 

11          0000000011 

25          0000000025 

7           0000000007 

... 

 

(29 row(s) affected)

Note that SQL Server 2012 introduces a new function called FORMAT that allows you to achieve 

such formatting needs much more easily. I’ll describe it later in this section.

www.it-ebooks.info

---