String Functions – SUBSTRING()

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.

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.

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.

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.

Related posts

Leave a Comment