通過單個選擇sql中的特定分隔符將具有奇怪字串的一列拆分為多列

我正在尋找/希望找到一個更簡單的解決方案，盡管我已經有了一個可行的解決方案。

但我很難接受，這是唯一的方法。因此我希望，一個好的 sql poweruser 可能有更好的主意。

背景：

一個看起來像這樣的簡單表：

CREATE TABLE TESTTABLE (weirdstring varchar(MAX),
                        ID int);
INSERT INTO TESTTABLE
VALUES ('A;GHL BH;BC,NA-NB,[AB]', 1);
INSERT INTO TESTTABLE
VALUES ('B;GHL BH;BC,NA-NB,[AB]', 2);
INSERT INTO TESTTABLE
VALUES ('C;GHL BH;BC,NA-NB,[AB]', 3);

最后我需要的是來自奇怪字串的前3個“字母組”（1-3個字母）（例如，ID 1 = A，GHL和BH，字串的其余部分現在不重要）在單獨的列中：

到目前為止所做的是：

1. 將字串中所有奇怪的分隔符（; - 和可能的更多）更改為逗號，消除“字母組”周圍的括號。正在使用 REPLACE 菊花鏈。所以A;GHL BH;BC,NA-NB,[AB]從頭到尾 A,GHL,BH,BC,NA,NB,AB。

2. 以逗號作為分隔符將新字串拆分為列。

使用的查詢是：

SELECT t1.ID,
       t1.weirdstring,
       t2.group1,
       t2.group2,
       t2.group3
FROM TESTTABLE t1
     LEFT JOIN (SELECT grp1.ID,
                       grp1.weirdstring AS group1,
                       grp2.weirdstring AS group2,
                       grp3.weirdstring AS group3
                FROM (SELECT ID,
                             weirdstring
                      FROM (SELECT ID,
                                   weirdstring,
                                   ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS ROWNUM
                            FROM (SELECT ID,
                                         value AS weirdstring
                                  FROM TESTTABLE
                                       CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(weirdstring, '[', ''), ']', ''), ';', ','), ' ', ','), '-', ','), '.', ','), ',')
                                  WHERE weirdstring IS NOT NULL) splitted ) s1
                      WHERE ROWNUM = 1) grp1
                     LEFT JOIN (SELECT ID,
                                       weirdstring
                                FROM (SELECT ID,
                                             weirdstring,
                                             ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS ROWNUM
                                      FROM (SELECT ID,
                                                   value AS weirdstring
                                            FROM TESTTABLE
                                                 CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(weirdstring, '[', ''), ']', ''), ';', ','), ' ', ','), '-', ','), '.', ','), ',')
                                            WHERE weirdstring IS NOT NULL) splitted ) s2
                                WHERE ROWNUM = 2) grp2 ON grp1.ID = grp2.ID
                     LEFT JOIN (SELECT ID,
                                       weirdstring
                                FROM (SELECT ID,
                                             weirdstring,
                                             ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS ROWNUM
                                      FROM (SELECT ID,
                                                   value AS weirdstring
                                            FROM TESTTABLE
                                                 CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(weirdstring, '[', ''), ']', ''), ';', ','), ' ', ','), '-', ','), '.', ','), ',')
                                            WHERE weirdstring IS NOT NULL) splitted ) s3
                                WHERE ROWNUM = 3) grp3 ON grp3.ID = grp2.ID) t2 ON t1.ID = t2.ID;

但我無法相信最終為我的小任務創建了多少查詢。至少我相信它很小。我使用的是舊版本 (14) 的 sql-server，因此我不能將string_split與它的第三個引數 (enable-ordinal) 一起使用 語法：

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

注意：https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16：當前支持 enable_ordinal 引數和有序輸出列在 Azure SQL 資料庫、Azure SQL 托管實體和 Azure Synapse Analytics（僅限無服務器 SQL 池）中。從 SQL Server 2022 (16.x) Preview 開始，引數和輸出列在 SQL Server 中可用。

還有其他更短的方法可以達到相同的結果嗎？我知道這個話題已經討論過很多次了，但是我在這里找不到解決我的具體問題的方法。提前感謝您的任何幫助！

uj5u.com熱心網友回復：

您似乎使用的是 SQL Server 2017 (v.14)，因此可能的選項是以下基于 JSON 的方法。想法是將存盤的文本轉換為有效的 JSON 陣列 ( A;GHL BH;BC,NA-NB,[AB]into ["A","GHL","BH","BC","NA","NB","AB"])，TRANSLATE()用于字符替換，并使用以下方法獲取字串的預期部分JSON_VALUE()：

SELECT
   weirdstring,
   JSON_VALUE(jsonweirdstring, '$[0]') AS group1,
   JSON_VALUE(jsonweirdstring, '$[1]') AS group2,
   JSON_VALUE(jsonweirdstring, '$[2]') AS group3
FROM (  
   SELECT 
      weirdstring,
      CONCAT('["', REPLACE(TRANSLATE(weirdstring, '; -,[]', '######'), '#', '","'), '"]') AS jsonweirdstring
   FROM TESTTABLE
) t  

標籤：sql sql服务器 sql-server-2017

上一篇：更新列與另一個表匹配的SQLServer表

下一篇：如何在SQLServer中插入表而不是選擇