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

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.

CodePudding user response：

Here is another method that is using tokenization via XML and XQuery.

The XPath predicate [position() ge 3] is asking to get all tokens starting from the 3rd.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Product VARCHAR(100));
INSERT @tbl (Product) VALUES
('LA100_Runner_35C924_D'),
('MA200_Runner_77C924_D');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '_';

SELECT t.* 
    , Result = REPLACE(c.query('data(/root/r[position() ge 3])')
        .value('text()[1]', 'VARCHAR(100)'), SPACE(1), @separator)
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
      REPLACE(Product, @separator, ']]></r><r><![CDATA[')   
      ']]></r></root>' AS XML)) AS t1(c);

Output

ID	Product	Result
1	LA100_Runner_35C924_D	35C924_D
2	MA200_Runner_77C924_D	77C924_D

CodePudding user response：

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.