SQL Server SUBSTRING Function with Examples

← PrevNext →

In one of my previous articles, I have explained the functionalities of CHARINDEX function with examples. Along with it I also have briefly described the use of SQL Server SUBSTRING() function too. An SQL Server SUBSTRING function will extract a part of string from a string. This function will help you locate a single or multiple characters from a phrase or an entire sentence.

sql server substring function

Syntax

SUBSTRING (value_expression, start_location, length)

value_expression

The first parameter (also called an argument) is the actual string in which the specified string or character has to be looked.

SELECT Value = SUBSTRING('LIGHT YEARS AWAY', 7, 5)

The result is YEARS

The value LIGHT YEARS AWAY (highlighted) in the above example is the first parameter and the source string.

start_location

We need to mention a starting location to begin our search for the specified string or character. The second parameter in the SUBSTRING function is an Integer value.

Note: The spaces in the string, also considered as valid value while locating the specified characters.

length

The third parameter is also an Integer value, which specifies the length of the string that is searched. The total length should not be more than the length of source string else, the result will be nothing.

Example

WITH Books AS (SELECT 'LIGHT YEARS AWAY' AS BookName)
SELECT SUBSTRING(BookName, 7, LEN(BookName)) The_Value FROM Books

The result is YEARS AWAY

We have not specified any Integer value as our third parameter, since we want the search to begin from seventh position covering the entire string. We have used the LEN() function, which returns the total number of characters in a given string.



SUBSTRING with “WHERE” clause

SQL Server string functions can also be used in where clause. It is very simple; all you need to know is what to look for in a string.

WITH Books AS (SELECT 'LIGHT YEARS AWAY' AS BookName)
SELECT *FROM Books 
WHERE SUBSTRING(BookName, 7, 5) = 'YEARS'

The result is LIGHT YEARS AWAY.

Conclusion

Normally the result of SUBSTRING function can be collaborated by an outer query, which in turn makes the result set more useful. We can use this function in a variety of queries and all this is possible due to its flexibility.

← PreviousNext →