SQL Substring – Selecting Specific Field Data

We can write a SQL query using the substring function to select the exact pieces of information needed in a field. Depending on the database we would use either the SUBSTR() or SUBSTRING() methods. Almost all SQL databases will use one or the other.

We can look at the following syntax examples:

Oracle SQL Syntax

SELECT SUBSTR(<column_name>,<start_position>,<character_length>) AS ColumnName FROM Table;

SQL Server Syntax

SELECT SUBSTRING(<column_name>,<start_position>,<character_length>) AS ColumnName FROM Table;

Practical Examples of Substring

It’s important to note that the starting position can also be a negative value. This allows us to substring characters at the end of a field rather than the beginning. As an example if we wanted the last character in a string we could do the following:

SELECT SUBSTRING(myColumn,-1,1) AS LastCharacter FROM Table;

Let’s imagine a scenario where we need a new field. We only want to have the initials of a customer for display purposes. We could write a SQL query to do that while using the substring function. Using a SQLite database in DBeaver we can accomplish our task like the following.

To validate the output we can add back in the first name and last name fields to view the results.

This is just one of many uses for SUBSTRING. Yet another great SQL function to add to the tool-belt.

Final Thoughts & Other Use Cases

Often times, the SUBSTRING method is used in conjunction with other functions such as LENGTH or REPLACE. Of course, this all depends on the use case. There are many reasons why we might want to use SUBSTRING including, but not limited to:

To extract first and last names from a full name field
To extract initials for a name
To extract email information from a field
To trim field data
The list goes on...

Practical Examples of Substring

Final Thoughts & Other Use Cases

Related