SUBSTRING (value_expression, start_location, length)
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
The value LIGHT YEARS AWAY (highlighted) in the above example is the first parameter and the source string.
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.
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.
WITH Books AS (SELECT 'LIGHT YEARS AWAY' AS BookName) SELECT SUBSTRING(BookName, 7, LEN(BookName)) The_Value FROM Books
The result is
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.
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.