我想通過 SQL（無 T-SQL）從以下字串中提取 Id
XY_FOO_BAR1=123;XY_FOO_BAR2=456;XY_FOO_BAR3=789;
結果應如下所示：
123
456
789
使用 T-SQL 會很容易，但是如何使用純 SQL 解決問題？我已經用PATINDEX.CHARINDEXSUBSTRING
我將不勝感激任何提示。
最終解決方案感謝@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
uj5u.com熱心網友回復：
試過這個，似乎沒問題。但order by value似乎很棘手
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
uj5u.com熱心網友回復：
希望這會有所幫助
=======
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/478961.html