Substring function in SQL Server 2012

T-SQL provides functions which operate on strings. One of this functions is SUBSTRING. This function returns new substring, get 3 parameters: string, start index, length of substring. Below You may see several examples with this function.

First example returns substring from abracadabra string start in 1 index(first char) and get 4 chars.

SELECT 'abracadabra' as stringname,
        SUBSTRING('abracadabra',1,4) as substringname ;

z1
The function returned four- char substring.

Second example returns substring from abracadabra string start in 1 index(first char) and get 100 chars – more then length of given string.

SELECT 'abracadabra' as stringname,
        SUBSTRING('abracadabra',1,100) as substringname ;

z27
The function returned max length of chars from string start from first char.

Third example returns substring from abracadabra string start in 1 index(first char) and get 0 chars – zero chars of given string.

SELECT 'abracadabra' as stringname,
        SUBSTRING('abracadabra',1,0) as substringname ;

z358
The function don’t returned char from string.

Fourth example returns substring from abracadabra string start in 1 index(first char) and get 1 chars from given string.

SELECT 'abracadabra' as stringname,
        SUBSTRING('abracadabra',1,1) as substringname ;

z46

Fifth example returns substring from abracadabra string start in 0 index(this function sign 1 index as first char in string) and get 1 char from given string.

SELECT 'abracadabra' as stringname,
        SUBSTRING('abracadabra',0,1) as substringname ;

z358
The function don’t returned char from string.

Sixth example returns substring from abracadabra string start in 0 index(this function sign 1 index as first char in string) and get 2 chars from given string.

SELECT 'abracadabra' as stringname,
        SUBSTRING('abracadabra',0,2) as substringname ;

z46

Seventh example returns substring from abracadabra string start in 0 index(this function sign 1 index as first char in string) and get 100 chars – more then length of given string.

SELECT 'abracadabra' as stringname,
        SUBSTRING('abracadabra',0,100) as substringname ;

z27

Eighth example returns substring from abracadabra string start in 0 index(this function sign 1 index as first char in string) and get 0 chars – zero chars of given string.

SELECT 'abracadabra' as stringname,
        SUBSTRING('abracadabra',0,0) as substringname ;

z358
The function don’t returned char from string.

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.