SQL Server String Functions
SQL Server string functions are scalar functions that perform an operation on a string input value and return a string or numeric value.
REPLICATE Function
The REPLICATE function repeats a given string specified number of times. The syntax is: REPLICATE(string, number of times). For example, the following query prints the string '100' five times:
SELECT REPLICATE('100', 5)
Result:
100100100100100
One common usage of REPLICATE function is to combine it with other string functions and replace leading or trailing spaces with another character.
DECLARE @StringWithLeadingSpaces VARCHAR(10)
SELECT @StringWithLeadingSpaces= ' SD3L6AA'
SELECT @StringWithLeadingSpaces = REPLICATE('0', LEN(@StringWithLeadingSpaces)
- LEN(LTRIM(@StringWithLeadingSpaces))) + LTRIM(@StringWithLeadingSpaces)
SELECT @StringWithLeadingSpaces AS StringWithOUTLeadingSpaces
Result:
StringWithOUTLeadingSpaces
00000SD3L6
Tags: SQL Server String Functions SQL SQL Server Functions. Character String Data Types and Functions with Sample SQL Statements. Quick reference, Data Type Tables, Function Tables.
SQL Server string functions are scalar functions that perform an operation on a string input value and return a string or numeric value.
REPLICATE Function
The REPLICATE function repeats a given string specified number of times. The syntax is: REPLICATE(string, number of times). For example, the following query prints the string '100' five times:
SELECT REPLICATE('100', 5)
Result:
100100100100100
One common usage of REPLICATE function is to combine it with other string functions and replace leading or trailing spaces with another character.
DECLARE @StringWithLeadingSpaces VARCHAR(10)
SELECT @StringWithLeadingSpaces= ' SD3L6AA'
SELECT @StringWithLeadingSpaces = REPLICATE('0', LEN(@StringWithLeadingSpaces)
- LEN(LTRIM(@StringWithLeadingSpaces))) + LTRIM(@StringWithLeadingSpaces)
SELECT @StringWithLeadingSpaces AS StringWithOUTLeadingSpaces
Result:
StringWithOUTLeadingSpaces
00000SD3L6