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.
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
Here is the result:
| identifier |
| 123 |
| 2345678 |
| 34 |