SQL REPLICATE Function

REPLICATE repeates a string a specified number of times.

Can be used for generating product codes, order numbers, lines, etc.

Example

#

The example returns the string '0-' repeated 10 times.

SELECT REPLICATE('0-', 10) AS Replicated
Result:  1 record
Replicated
0-0-0-0-0-0-0-0-0-0-

Syntax

Syntax of the REPLICATE function.

REPLICATE(string, value)

string -- a character string to be repeated.

value -- an integer specifying the number of times to repeat the string.


More Examples

REPLICATE with OrderNumber

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Generate Invoice numbers by prefixing OrderNumber with three zeros.
SELECT FirstName, LastName, OrderNumber, 
       REPLICATE('0', 3) + '-' + OrderNumber
       AS 'Invoice Number', 
       TotalAmount
  FROM [Order] O
  JOIN Customer C ON O.CustomerId = C.Id
Result:  830 records
FirstName LastName OrderNumber Invoice Number TotalAmount
Paul Henriot 542378 000-542378 440.00
Karin Josephs 542379 000-542379 1863.40
Mario Pontes 542380 000-542380 1813.00
Mary Saveley 542381 000-542381 670.80
Pascale Cartrain 542382 000-542382 3730.00

You may also like



Guides


vsn 3.1