In this tutorial, you’ll see how to apply LEFT, RIGHT and SUBSTRING in SQL Server.
In particular, you’ll observe how to extract specific characters:
- From the left
- From the right
- From the middle
- Before a symbol
- Before space
- After a symbol
- Between identical symbols
- Between different symbols
8 scenarios of LEFT, RIGHT and SUBSTRING in SQL Server
For each of the scenarios to be reviewed, the ultimate goal is to extract only the digits within the strings. For example, for the string of ‘12345-abc‘ the goal is to extract only the digits of 12345.
For illustration purposes, a database (called TestDB) will be created. The database would contain 8 tables to represent the 8 scenarios below:
(1) Extract characters from the LEFT
You can use the following template to extract characters from the LEFT:
LEFT(field_name, number of characters to extract from the left)
Suppose that you created a table in SQL Server (called dbo.table_1) that includes the following 3 strings:
You can then run the following query to extract the 5 digits from the left (under the ‘Identifier’ field):
SELECT LEFT(identifier,5) AS identifier FROM TestDB.dbo.table_1
Once you run the query, you’ll get only the 5 digits from the left:
(2) Extract characters from the RIGHT
You can use this template to extract characters from the RIGHT:
RIGHT(field_name, number of characters to extract from the right)
Now let’s suppose that you created a new table called dbo.table_2 with the following 3 strings:
You may then extract the 5 digits from the right using this query:
SELECT RIGHT(identifier,5) AS identifier FROM TestDB.dbo.table_2
Run the query, and you’ll see the 5 digits from the right:
(3) Extract characters from the Middle
You may use SUBSTRING to extract characters from the middle:
SUBSTRING(field_name, starting position, ending position relative to the starting position)
Let’s create a third table called dbo.table_3. As you can see, the digits are now located in the middle of the strings:
In order to get only the digits in the middle, you may run this query:
SELECT SUBSTRING(identifier,4,5) AS identifier FROM TestDB.dbo.table_3
You’ll now get the digits from the middle:
(4) Before a symbol
What if you want to get all the characters before a symbol, such as the hyphen symbol?
In that case, you may use:
LEFT(field_name,CHARINDEX('symbol needed', field_name) - 1)
Let’s create a fourth table called dbo.table_4:
The goal is to extract all the digits before the hyphen symbol (‘-‘) for varying-length strings.
Here is the query that you may run:
SELECT LEFT(identifier,CHARINDEX('-', identifier) - 1) AS identifier FROM TestDB.dbo.table_4
And here is the result:
(5) Before space
Under this scenario, the table dbo.table_5 will be created:
The goal is to get all the digits before the space.
You can use the same template as in the previous scenario, but rather than specifying the symbol needed, just leave an empty space within the CHARINDEX function:
SELECT LEFT(identifier,CHARINDEX(' ', identifier) - 1) AS identifier FROM TestDB.dbo.table_5
You’ll now get all the digits before the space:
(6) After a symbol
You may use the following syntax in order to get all the characters after a symbol (for varying-length strings):
RIGHT(field_name,CHARINDEX('symbol needed', (REVERSE(field_name))) - 1)
Let’s now create a new table called dbo.table_6:
And here is the query that you may run to extract all the digits after the hyphen symbol:
SELECT RIGHT(identifier,CHARINDEX('-', (REVERSE(identifier))) - 1) AS identifier FROM TestDB.dbo.table_6
The result:
(7) Between identical symbols
Suppose that you have the following dbo.table_7 table:
The goal is to get all the digits between the two identical symbols (the hyphen symbols in this example).
Here is the complete query to accomplish this goal:
SELECT SUBSTRING(identifier,LEN(LEFT(identifier,CHARINDEX('-', identifier)+1)),LEN(identifier) - LEN(LEFT(identifier,CHARINDEX('-', identifier))) - LEN(RIGHT(identifier,CHARINDEX('-', (REVERSE(identifier)))))) AS identifier FROM TestDB.dbo.table_7
As you can see, only the digits between the two symbols got extracted:
(8) Between different symbols
For the final scenario, let’s create the dbo.table_8 table:
You may then use this query to get all the digits between the ‘-‘ symbol and the ‘@’ symbol:
SELECT SUBSTRING(identifier,LEN(LEFT(identifier,CHARINDEX('-', identifier)+1)),LEN(identifier) - LEN(LEFT(identifier,CHARINDEX('-', identifier))) - LEN(RIGHT(identifier,CHARINDEX('@', (REVERSE(identifier)))))) AS identifier FROM TestDB.dbo.table_8
Here is the result: