extract text from string data using SQL (with handing null values)-CodePudding

I have issue with extracting text from string data using T-SQL. I have following data and text that I would like to extract (value between second and third underscore:

sss_ss_d_a -> d
aaa_dd_b -> b
aaa_aa -> NULL

I know that there is a lot of similar topics, but I have problem especially with handling NULLs and situation when there are no delimiter after desired value

Thank you very much for your help, regards

CodePudding user response：

Try it like this:

Some sample data in a declared table

DECLARE @tbl TABLE(YourString VARCHAR(100));
INSERT INTO @tbl VALUES('sss_ss_d_a'),('aaa_dd_b'),('aaa_aa');

--The query

SELECT t.YourString
      ,CAST(CONCAT('<x>',REPLACE(t.YourString,'_','</x><x>'),'</x>') AS XML).value('/x[3]/text()[1]','nvarchar(max)')
FROM @tbl t;

The idea in short:

We replace the underscores with XML tags thus transforming the string to castable XML.
We use XQuery within .value to pick the third element.

Starting with v2016 the recommended approach uses JSON instead
(hint: we use "2" instead of "3" as JSON is zero-based).

      ,JSON_VALUE(CONCAT('["',REPLACE(t.YourString,'_','","'),'"]'),'$[2]')

The idea is roughly the same.

General hint: Both approaches might need escaping forbidden characters, such as < or & in XML or " in JSON. But this is easy...