SQL Substring Function

The SQL Substring function enables us to extract a section of a string. The syntax of this function (and even its name) varies depending on which database you are using. In SQL Server the syntax is as follows:

SUBSTRING(String, StartPosition, NumberOfCharacters)

The String parameter can be a ColumnName, a variable name or a literal string value (enclosed in quotes).

The StartPosition states at what position in the string do we want to start extracting a substring from. The NumberOfCharacters parameter specifies how many characters to extract from the starting character.

An example of this in use would be:

-- SQL Server

-- passing the string as a variable
DECLARE @String1 varchar(10)
SET @String1 = 'This is a String'
SELECT SUBSTRING(@String1, 6, 4)

-- passing a column name as a string
SELECT SUBSTRING(Telephone, 5, 3) FROM Customers

-- passing a literal string value
SELECT SUBSTRING('25/03/2007', 4, 2) As UKMonth

The first example in the above code would extract the substring 'is a' from the string variable @String1.

In other databases, the SUBSTRING function is known under a different name as detailed below. Note for the databases listed below, the NumberOfCharacters parameter can be optionally omitted to return all characters after the specified StartPosition.

Microsoft Access: MID()
ORACLE: SUBSTR()
MySQL: SUBSTR() or SUBSTRING()

SELECT SUBSTR('20/03/2007', 4)

The above example for Oracle / MySQL would extract the '03/2007' from the string '20/03/2007'

Conclusion

In this article, we have identified the SUBSTRING function as being able to extract a portion of a string that starts at a certain point in the original string and extends for a specified number of characters. We have also seen how equivalent functions are found in other database systems but that they exist under different names.