I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the SPACE() function today.
The SPACE() function takes a positive integer replication count [C] as input. The function returns a string composed of a single space concatenated [C] times to itself as output.
The example below adds one, two, and three spaces inside the title of a favorite movie of mine.
|
1 2 |
-- Add 1 + 2 + 3 spaces select space(1) + 'wizard' + space(2) + 'of' + space(3) + 'oz' as str_phrase |
|
1 2 3 4 5 |
output: str_phrase ---------------- wizard of oz |
One thing to always 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 select space(-1) as str_word |
|
1 2 3 4 5 |
output: str_word --------- NULL |
Next time, I will be exploring the STR() function. This function was heavily used before the FORMAT() function was released in SQL Server 2012.
