Home How can I get all of them after the second "_" separation in a string data in mssql?

Questions

How can I get all of them after the second "_" separation in a string data in mssql?

November 2, 2022

I have data like this in a string column in a table: [Product] -> "LA100_Runner_35C924_D". From this data I want to get the data after the second _, so I want to get 35C924_D.

How do I do that?

I tried WHERE [Product] LIKE '%_%' escape '' but I couldn’t quite get it working. I can’t think of what I want with the LIKE operation.

>Solution :

One option is to apply the combination of SUBSTRING + PATINDEX twice on the same strings, while splitting on the first underscore symbol as follows:

WITH cte AS (
    SELECT SUBSTRING(string, PATINDEX('%[_]%', string)+1, LEN(string)) AS string
    FROM tab
)
SELECT SUBSTRING(string, PATINDEX('%[_]%', string)+1, LEN(string))
FROM cte

Check the demo here.

You can also do the same using RIGHT + PATINDEX in a very similar fashion:

WITH cte AS (
    SELECT RIGHT(string, LEN(string) - PATINDEX('%[_]%', string)) AS string
    FROM tab
)
SELECT RIGHT(string, LEN(string) - PATINDEX('%[_]%', string)) AS string
FROM cte

Check the demo here.