SQL Server: basic functions (part 9) - Substring

Tweet

One of the most interesting Sql function, when working with strings, is Substring. Basically, it returns a part of a string, given the starting point and the length (number of characters).

The syntax
Let's see Substring syntax:

SUBSTRING ( expression , start , length )

In the function we have to declare:

1. expression, which is the string we want to manipulate;
2. start, which is an integer or bigint indicating where to start the extraction. If it is 0 (zero), the starting point will be at the beginning of the expression -1 (see below in "examples"). If start is greater than the length of expression, the function will return a zero-length expression;
3. length, which is a positive integer or bigint indicating how many characters to consider from the start (starting point).

Only strings?

Substring is not only working with strings, in fact expression can be char, varchar, text, nchar, nvarchar, ntext and also binary, varbinary and image. Basically, the returned values are strictly related to the expression data type. If the expression is char, varchar or text, the function will return varchar. If the expression is nchar, nvarchar or ntext, the function will return nvarchar. If the expression is binary, varbinary or image, the function will return varbinary. Therefore, start and length must indicate the number of characters or bytes (for text, image, binary or varbinary data types).

Examples

Just as a simple example,

SUBSTRING('Hello!', 3, 4)

will return

llo!

Another example would be:

SUBSTRING('Hello!', 1, 2)

that returns

He

Strangely enough, there are some odd behaviours with the function when start is either zero or a negative number.

SUBSTRING('Hello!', 0, 3)

will return

He

And

SUBSTRING('Hello!', -1, 3)

will return

H

That's all for now. See you next time!