SQL Server:如何在最后一次出现另一个子字符串之后和下一个逗号之前找到子字符串 [英] SQL Server: how to find the substring just after the last occurence of another substring and before the next comma

问题描述

我在 SQL Server 数据库中有一个表，其中有一列 ColumnStrings 带有此类字符串:

I have a table in a SQL Server database which has a column ColumnStrings with strings of this kind:

"AB=ikkw0116,AC=BE D Work stations,AC=BE D stations,AC=D Allocated,AD=pnser,AD=pnsas,AD=owned,AD=increased"
"AB=ikkWA001S1,AC=BE D HD,AC=D Allocated,AD=pnser,AD=pnsas,AD=owned,AD=increased"
"AB=iksw0084,AC=Domain View,AD=pnsas,AD=owned,AD=increased"
"AB=GHRS05900263,AC=Big stations,AC=GHR,AC=BE,AD=ger,AD=eu,AD=intra"

所以我们有一个随机数 AB=、AC= 和 AD=.

So we have a random number of AB=, AC= and AD=.

我想在最后一次出现 AC= 之后获取值(位于等号之后和下一个昏迷之前的子字符串).

I would like to get the value (substring placed after the equal sign and before the next coma) just after the last occurrence of AC=.

在我的示例中，搜索的值将针对这 4 个字符串:

In my example, the searched values would be for those 4 strings:

"D Allocated"
"D Allocated"
"Domain View"
"BE"

我可以用

DATALENGTH(MyTable.[ColumnStrings])-CHARINDEX(REVERSE('=AC'),REVERSE(MyTable.[ColumnStrings]))-1

但是如何在此 =AC 之后和下一个逗号之前获取子字符串(如果我们找不到任何逗号，则在字符串的末尾)

but how to get the substring just after this =AC and before the next comma (or the end of the string if we don't find any comma)

推荐答案

在像您这样的情况下，基于 JSON 的方法是一种可能的选择.您需要将输入字符串适当地转换为有效的 JSON 结构 - 嵌套的 JSON 数组 (AB=iksw0084,AC=Domain View,AD=pnsas,AD=owned,AD=increased 到 [[AB"、iksw0084"]、[AC"、域视图"]、[AD"、pnsas"]、[AD"、拥有"]、[AD"，增加"]).然后您需要使用 OPENJSON() 和默认模式解析这个 JSON.结果是一个包含 key、value 和 type 列的表，如果是数组，key 列包含数组中每个项目的从 0 开始的索引.想法是将此索引用于 ROW_NUMBER() 调用中的 ORDER BY 子句.

In situation like yours, a JSON-based approach is a possible option. You need to transform appropriately the input strings into a valid JSON structure - a nested JSON arrays (AB=iksw0084,AC=Domain View,AD=pnsas,AD=owned,AD=increased into [["AB","iksw0084"],["AC","Domain View"],["AD","pnsas"],["AD","owned"],["AD","increased"]). Then you need to parse this JSON with OPENJSON() and default schema. The result is a table with columns key, value and type and in case of an array the key column holds the 0-based index of each item in the array. The idea is to use this index for the ORDER BY clause in the ROW_NUMBER() call.

表格:

SELECT ColumnStrings
INTO Data
FROM (VALUES 
   ('AB=ikkw0116,AC=BE D Work stations,AC=BE D stations,AC=D Allocated,AD=pnser,AD=pnsas,AD=owned,AD=increased'),
   ('AB=ikkWA001S1,AC=BE D HD,AC=D Allocated,AD=pnser,AD=pnsas,AD=owned,AD=increased'),
   ('AB=iksw0084,AC=Domain View,AD=pnsas,AD=owned,AD=increased'),
   ('AB=GHRS05900263,AC=Big stations,AC=GHR,AC=BE,AD=ger,AD=eu,AD=intra')
) v (ColumnStrings)

声明:

SELECT j.StringValue
FROM Data d
OUTER APPLY (
   SELECT 
      j1.[value], 
      JSON_VALUE([value], '$[0]') AS StringKey, 
      JSON_VALUE([value], '$[1]') AS StringValue,
      ROW_NUMBER() OVER (
         PARTITION BY JSON_VALUE([value], '$[0]') 
         ORDER BY CONVERT(int, [key]) DESC
      ) AS RN
   FROM OPENJSON(CONCAT('[["', REPLACE(REPLACE(d.ColumnStrings, ',', '"],["'), '=', '","'), '"]]')) j1
) j
WHERE j.StringKey = 'AC' AND j.RN = 1

结果:

StringValue
-----------
D Allocated
D Allocated
Domain View
BE

这篇关于SQL Server:如何在最后一次出现另一个子字符串之后和下一个逗号之前找到子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！