SQL Server SUBSTRING() function with example

Last updated: 5th March 2024

The SUBSTRING() function extracts a "substring" (or, a set characters) from another string, starting from x position with a given length. In one of my previous tutorials, I have explained about CHARINDEX() function with an example and I have briefly described the SUBSTRING() function too. Let's understand how the SUBSTRING() function works.

sql server substring function

Syntax

SUBSTRING (value_expression, start_location, length)

value_expression

The first parameter (or argument) is the actual "string" in which a particular string or character has to be looked. For example,

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

The output is: YEARS

The string "LIGHT YEARS AWAY" in the above example is the first argument in the function and is also the source string.

start_location

We need to provide the starting location to begin our search for the specified string or character. The "second" argument 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 argument 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.

Now, let's see another example.

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

The output 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 output is: LIGHT YEARS AWAY.

Using SUBSTRING() with CHARINDEX()

You can use the SUBSTRING() with CHARINDEX() function to produce some amazing results.

WITH TAG AS (SELECT 'PHP | JAVA | PYTHON' AS Category)
SELECT SUBSTRING(Category, 1, CHARINDEX('PYTHON', Category) - 3) Category
FROM TAG

The output is: PHP | JAVA

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 →