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.

Here are the 8 scenarios:

(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 table_1) that includes the following 3 strings:

identifier
12345-abc
67895-xyz
45678-mmm

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 table_1

Once you run the query, you’ll get only the 5 digits from the left:

identifier
12345
67895
45678

(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 table_2 with the following 3 strings:

identifier
ID-12345
ID-67895
ID-45678

You may then extract the 5 digits from the right using this query:

SELECT  
RIGHT(identifier,5) AS identifier
FROM table_2

Run the query, and you’ll see the 5 digits from the right:

identifier
12345
67895
45678

(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 table_3. As you can see, the digits are now located in the middle of the strings:

identifier
ID-12345-END
ID-67895-END
ID-45678-END

In order to get only the digits in the middle, you may run this query:

SELECT  
SUBSTRING(identifier,4,5) AS identifier
FROM table_3

You’ll now get the digits from the middle:

identifier
12345
67895
45678

(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 table_4:

identifier
123-IDAA
2345678-IDB
34-IDCCC

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 table_4

And here is the result:

identifier
123
2345678
34

(5) Before space

Under this scenario, the table table_5 will be created:

identifier
123 IDAA
2345678 IDB
34 IDCCC

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 table_5

You’ll now get all the digits before the space:

identifier
123
2345678
34

(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 table_6:

identifier
IDAA-123
IDB-2345678
IDCCC-34

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 table_6

The result:

identifier
123
2345678
34

(7) Between identical symbols

Suppose that you have the following table_7 table:

identifier
IDAA-123-AB
IDB-2345678-B
IDCCC-34-CDE

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 table_7

As you can see, only the digits between the two symbols got extracted:

identifier
123
2345678
34

(8) Between different symbols

For the final scenario, let’s create the table_8 table:

identifier
IDAA-123@AB
IDB-2345678@B
IDCCC-34@CDE

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 table_8

And here is the result:

identifier
123
2345678
34

Leave a Comment