如何使用分界符从文本字段中提取n-th值

Question

在SQL表中，我有一个带有值'Yellow | green | blue'的文本列，另一列具有数字值。该数值定义要提取文本列的哪一部分。文本列中的值用" |"分开分离器。

例如：如果数值为0，则应提取文本字段的第一部分：黄色

如果数值为1，则应提取文本字段的第二部分：绿色

等等。

有没有办法动态提取它？意思没有使用案例语句，例如：

case when u.UD_2 =0 then 'Yellow' when u.UD_2=1 then 'Green' when u.UD_2=3 then 'Blue' end Kategorie

更新：我们正在使用SQL Server 2016

Answer 1

这应该对您有用，在子查询中，在每个类别中进行分开的列，然后使用案例语句选择所需的类别。

select case sep when 0 then x.[0] when 1 then x.[1] when 2 then x.[2] end as Kategorie
from (
select *
    ,LEFT(val, CHARINDEX('|', val) - 1) AS '0'
    ,LEFT(STUFF(SUBSTRING(val, CHARINDEX('|', val), LEN(val)), 1, 1, ''), CHARINDEX('|', STUFF(SUBSTRING(val, CHARINDEX('|', val), LEN(val)), 1, 1, '')) - 1) AS '1'
    ,SUBSTRING(SUBSTRING(val, CHARINDEX('|', val), LEN(val)), CHARINDEX('|', val) + 1, LEN(val)) AS '2'
from #test
)x

样本数据：

create table #test 
(
    val nvarchar(500),
    sep int
)
insert into #test values 
('Yellow|Green|Blue', 0),
('Yellow|Green|Blue', 1),
('Yellow|Green|Blue', 2)

注意：这仅在用 |

分开的3个值时起作用

update

这是实现它的动态方法，并不重要，将分开多少类：

SELECT x.Kategorie 
FROM (
    SELECT DISTINCT node.s.value('.', 'NVARCHAR(500)') AS Kategorie
          ,ROW_NUMBER() OVER (PARTITION BY sep ORDER BY (SELECT NULL)) - 1 as rn 
    FROM (
        SELECT sep
            ,CAST('<M>' + REPLACE(val, '|', '</M><M>') + '</M>' AS XML) AS Kategorie
        FROM #test
        ) AS s
    CROSS APPLY Kategorie.nodes('/M') AS node(s)
)x
JOIN #test AS t ON t.sep = x.rn

Answer 2

一种可能的方法是将您的文本数据拆分为子字符串并获取每个子字符串位置。

从SQL Server 2016开始，您可以使用STRING_SPLIT()拆分字符串，但是在您的情况下，这不是一个选项，因为此功能返回带有所有substring的表，但没有订购，并且无法保证子字符串的顺序。

再次，如果您使用SQL Server 2016 ，则可以尝试将文本数据转换为使用REPLACE()('Yellow|Green|Blue'转换为'["Yellow","Green","Blue"]'(的有效JSON数组，然后将其使用默认架构使用OPENJSON()来检索此JSON数组表，具有key，value和type的列(key列包含指定数组中元素的索引(。

输入：

CREATE TABLE #Data (
   TextValue nvarchar(max),
   IndexValue int
)
INSERT INTO #Data
   (TextValue, IndexValue)
VALUES
   ('Yellow|Green|Blue', 0),
   ('Yellow|Green|Blue', 1)

t-sql：

SELECT d.TextValue, d.IndexValue, j.[value] AS [Value]
FROM #Data d
CROSS APPLY OPENJSON(CONCAT(N'["', REPLACE(d.TextValue, N'|', N'","'), N'"]')) j
WHERE d.IndexValue = j.[key]

输出：

---------------------------------------
TextValue           IndexValue  Value
---------------------------------------
Yellow|Green|Blue   0           Yellow
Yellow|Green|Blue   1           Green