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.