I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the REPLICATE() function today.
The REPLICATE() function takes a pattern string [S] and a integer replication count [C] as input. The function returns a string composed of pattern string [S] concatenated [C] times to itself as output.
The example below replicates a pattern ‘XO’ five times.
|
1 2 |
-- A string of XO's select replicate('XO', 5) as str_love; |
|
1 2 3 4 5 |
output: str_love --------- XOXOXOXOXO |
One thing to always worry about when using TSQL functions is how will it react with a EMPTY strings or NULL values? Both empty strings and NULL values as input return themselves as output.
|
1 2 3 4 5 |
-- Empty string returns empty string select replicate('', 10) as str_empty; -- Null returns Null select replicate(NULL, 10) as str_null; |
|
1 2 3 4 5 6 7 8 |
output: str_empty ---------- str_null --------- NULL |
Another thing to worry about when using TSQL functions is how will it react with out of range values. In the example below, a negative replication count returns a NULL string.
|
1 2 |
-- Invalid length returns NULL select replicate('-', -10) as str_dashes; |
|
1 2 3 4 5 |
output: str_dashes ---------- NULL |
Next time, I will be exploring the REVERSE() function.
