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 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.

Join our newsletter

Subscribe to our free weekly newsletter and get exclusive SQL tips and articles straight to your inbox. 

 Your email address is only used to send you our newsletter.