用分隔符SQL Server分隔文本

Question

我需要一些帮助来用分隔符/分隔文本，并获得第三个参数，我知道使用函数CHARINDEX、SUBSTRING、CASE, THEN, WHEN。。。这是可能的，但我遇到了困难。

数据库详细信息：

AE/SREW/SWERR/REEEM/RRTS
AI/SREW/RUYE/REMC/DOR
AR/SRTE/RIRRH/PROTRED
AE/ASDEE/RIRRH/PROTRED/SEDRR
AE/FGY/RH/VETSDEE/AIURD
TA/PA/RHNEW/SERJJ/CENTER/FERT
FJHDE
FJHDE/YREY

我运行了以下查询，但得到了以下结果：

SELECT
SUBSTRING(values, CHARINDEX('/', values, (CHARINDEX('/', values) + 1)) + 1, LEN(values)) AS Result
FROM
dbtable

结果：

SWERR/REEEM/RRTS
RUYE/REMC/DOR
RIRRH/PROTRED
RIRRH/PROTRED/SEDRR
HR/VETSDEE/AIURD
RHNEW/SERJJ/CENTER/FERT
FJHDE
FJHDE/YREY

期望结果：

ANSWER
RUYE
RIRRH
RIRRH
HR
RHNEW
FJHDE -> For cases where there is no "/", put the text 'OTHER'.
FJHDE/YREY -> For cases where there is "/" only one (1), "/", put the text 'OTHER'.

致以最良好的问候。

Answer 1

您可以将这些字符串转换为JSON数组的格式
然后从中选择第2个键(密钥从0开始(

SELECT ISNULL(a.value, 'OTHER') AS Result
FROM dbtable t
OUTER APPLY (
SELECT j.value
FROM OPENJSON('["'+REPLACE(t.[values],'/', '","')+'"]') j
WHERE j.[key] = 2
) a;

结果
SWERR
RUYE
RIRRH
RIRRH
RH
RHNEW
其他
其他

您将错误的长度传递给SUBSTRING函数，这就是为什么您将从第二个/直到最后得到结果。我用CROSS APPLY分解了第二和第三个/：

SELECT vals,
split2.idx, split3.idx,
CASE 
WHEN split2.idx = 0 THEN 'OTHER'
ELSE SUBSTRING(vals, split2.idx+1, split3.idx - split2.idx - 1)
END as third
FROM
tb
CROSS 
APPLY (SELECT CHARINDEX('/', vals, CHARINDEX('/', vals)+1) as idx) split2
CROSS
APPLY (SELECT CHARINDEX('/', vals, split2.idx+1) as idx) split3

或者，如果你在Azure SQL数据库上或使用SQL Server 2022，你可以简单地使用文档中描述的STRING_SPLIT，并从函数中返回值和序号。

如果你在SQL Server 2016或2019上，有STRING_SPLIT，但没有序数，你需要使用ROW_NUMBER() OVER (ORDER BY (SELECT 0))进行某种破解，因为函数并不能真正告诉你哪个字符串在哪个索引：

SELECT tb.vals, ISNULL(MAX(IIF(ordinal = 3, value, NULL)), 'OTHER')
FROM tb
CROSS
APPLY (SELECT value
, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) as ordinal 
FROM STRING_SPLIT(tb.vals, '/')
) split
GROUP BY tb.vals

其工作方式是使用STRING_SPLIT函数将每个值拆分为包含各个子字符串的行。然后使用按文字排序的ROW_NUMBER()来给出索引，尽管微软从未真正保证子字符串会按出现的顺序从函数中返回，这就是为什么它是一个黑客。然后，所有这些行都被聚合，在MAX聚合函数中，我们选择第三次出现。如果没有发现这样的情况，IIF语句将返回所有NULL，而MAX也将为NULL，然后我们根据要求将其替换为"OTHER"。

两种解决方案在dbfiddle上的工作演示