LEFT, RIGHT and SUBSTRING in SQL Server

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:

1. From the left
2. From the right
3. From the middle
4. Before a symbol
5. Before space
6. After a symbol
7. Between identical symbols
8. 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: