Extract a value between 2 pipes

August 24, 2023

My column has values

1234|aaa4|8752

The length of these values are not fixed.

I want to extract the second value aaa4

I tried writing this

SELECT SUBSTRING(column_name,CHARINDEX('|',column_name) + 1, 
CHARINDEX('|',column_name, CHARINDEX('|',column_name)))
FROM [name].[name].[table_name]

With this reference

Trying to extract number between 2 characters '|' MS SQL

But this is not working fine and it is showing text after the second pipe as well

aaa4|8752

It is because we are using substring and the third argument is of the length. So it is taking other characters as well. How do i limit the length to the second pipe?

>Solution :

SELECT SUBSTRING(
    column_name,
    CHARINDEX('|', column_name) + 1,
    CHARINDEX('|', column_name, CHARINDEX('|', column_name) + 1) - CHARINDEX('|', column_name) - 1
) AS extracted_value
FROM [name].[name].[table_name];

CHARINDEX('|', column_name) finds the position of the first pipe.
CHARINDEX('|', column_name, CHARINDEX('|', column_name) + 1) finds
the position of the second pipe, starting the search after the first
pipe.
The difference between the positions of the second pipe and the first
pipe (- 1) gives you the length of the substring you want to extract.