I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the SUBSTRING() function today.
The SUBSTRING() function allows a developer to slice and dice a string into pieces. It takes a source string [T], a integer starting position [S], and a the number of characters [C] to return as input parameters. It returns a string of [C] characters from position [S] as output.
The example below extracts one word from a famous movie title.
|
1 2 |
-- Extract one word select substring('wizard of oz', 8, 2) as str_word |
|
1 2 3 4 5 |
output: str_word -------- of |
One thing to worry about when using TSQL functions is how will it react with out of range values. A negative starting position [S] returns an empty string as output. A negative number of characters causing a system error. Please see the examples below.
|
1 2 3 4 5 |
-- Invalid start parameter return empty strings select substring('wizard of oz', -8, 2) as str_word -- Invalid length parameter select substring('wizard of oz', 8, -2) as str_word |
|
1 2 3 4 5 6 7 |
output: str_word -------- Msg 536, Level 16, State 1, Line 3 Invalid length parameter passed to the substring function. |
Another thing to always worry about when using TSQL functions is how will it react with a EMPTY strings or NULL values? A empty string as input results in a empty string as output. I was surprised to find out that a NULL string as input generates a system error.
|
1 2 3 4 5 |
-- Empty string returns itself select substring('', 8, 2) as str_word -- NULL string is invalid select substring(NULL, 8, 2) as str_word |
|
1 2 3 4 5 6 7 |
output: str_word -------- Msg 8116, Level 16, State 1, Line 2 Argument data type NULL is invalid for argument 1 of substring function. |
To recap, be careful when using this function. A invalid length [C] or NULL input string [T] will result in a system error. On the other hand, this function is great if you need to slice and dice a string.
I will be blogging about the UNICODE() function next time.
