Extract Id's from String with SQL-CodePudding

I want to extract the Id's from the following string via SQL (no T-SQL)

XY_FOO_BAR1=123;XY_FOO_BAR2=456;XY_FOO_BAR3=789;

The result should look like this:

123
456
789

With T-SQL it would be easy but how can I solve the problem with pure SQL? I have already tried a few things with PATINDEX, CHARINDEX and SUBSTRING but unfortunately this is quite complicated and it is certainly easier to implement.

I would be grateful for any tips.

final solution thanks @mtdot:

with List as (
    select v.value as keyValue
      from xy.foo cross apply STRING_SPLIT(stay_val,';') as v
     where id = 987654321 
)
select (select value from STRING_SPLIT(KeyValue, '=') order by value desc offset 1 rows fetch next 1 rows only) as Ids from List

CodePudding user response：

Tried with this and seems ok. But order by value seems tricky

declare @Raw nvarchar(max) = 'XY_FOO_BAR1=123;XY_FOO_BAR2=456;XY_FOO_BAR3=789;';
with List as (
    select value as KeyValue from STRING_SPLIT(@Raw, ';') where value != ''
)
select (select value from STRING_SPLIT(KeyValue, '=') order by value desc offset 1 rows fetch next 1 rows only) as Ids from List

CodePudding user response：

Hope this will help

=======

 declare @a varchar(100);
set @a = 'XY_FOO_BAR1=123;XY_FOO_BAR2=456;XY_FOO_BAR3=789;';
;with split1(whole) as
(
    select value from string_split(@a,';') where value <> ''
)
SELECT (select value from String_split(whole, '=') order by value desc offset 1 rows)  FROM  split1