SQL Book

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 sub string 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 the string variable @String1.

In other databases the SUBSTRING function is known as something else 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.