使用string_split將資料存盤在多列中，而不僅僅是一列？

我正在嘗試將 string2 ='DOB;Mar 1199;passport;AW1234567' 的資料保存到表格的多個列中，但它只將其移動到第一列。我正在使用 string_split 函式來分隔用“;”分隔的所有 4 個字串部分。我應該怎么做才能將此資料移動到跨 4 列的單行中？請參閱下面的詳細資訊和結果影像：

use TEST
DECLARE @string2 varchar(max);
DECLARE @sep char(1);
set @string2 = 'DOB;Mar 1199;passport;AW1234567';
set @sep = ';'
DECLARE @myTableVariable TABLE (id INT IDENTITY(1,1) PRIMARY KEY, name varchar(max))

insert into @myTableVariable
(name)
SELECT value FROM STRING_SPLIT(@string2, @sep);

print @string2;


insert into EMPLOYEE (dob1)
select name from @myTableVariable 

uj5u.com熱心網友回復：

為了保證列排序，您不能依賴string_split所以需要一個不同的用戶定義函式。這一個回傳相同的值列以及用于行排序的seq列：

create function dbo.SplitString(@string varchar(max), @Delimiter varchar(1))
returns table
as
   return(  
    select j.[value], 1   Convert(tinyint,j.[key]) Seq 
      from OpenJson(Concat('["',replace(@string, @delimiter , '","'),'"]')) j
   );

然后，您可以按如下方式使用它從示例字串創建列并插入到目標表中：

declare @string2 varchar(max)='DOB;Mar 1199;passport;AW1234567', @sep char(1)=';'

insert into Employee(Dob1, DobNum, Pass1, PassNum)
select 
    Max(case when Seq=1 then Value end) Dob1,
    Max(case when Seq=2 then Value end) DobNum,
    Max(case when Seq=3 then Value end) Pass1,
    Max(case when Seq=4 then Value end) PassNum
from dbo.SplitString(@string2, @sep);

示例小提琴

uj5u.com熱心網友回復：

SQL Server 內置PARSENAME()函式可以很方便地完成這項任務。

SQL

DECLARE @string2 varchar(max) = 'DOB;Mar 1199;passport;AW1234567';
DECLARE @sep char(1) = ';'
    , @dot CHAR(1) = '.';
DECLARE @employee TABLE (
    id INT IDENTITY(1,1) PRIMARY KEY, 
    Dob1 varchar(20), 
    DobNum VARCHAR(20),
    Pass1 VARCHAR(20),
    PassNum VARCHAR(20)
);

WITH rs AS
(
    SELECT REPLACE(@string2, @sep, @dot) AS tokenList
)
INSERT INTO @employee (Dob1, DobNum, Pass1, PassNum)
SELECT PARSENAME(tokenList,4)
    , PARSENAME(tokenList,3)
    , PARSENAME(tokenList,2)
    , PARSENAME(tokenList,1)
FROM rs;

-- test
SELECT * FROM @employee;

輸出

 ---- ------ ---------- ---------- ----------- 
| id | Dob1 |  DobNum  |  Pass1   |  PassNum  |
 ---- ------ ---------- ---------- ----------- 
|  1 | DOB  | Mar 1199 | passport | AW1234567 |
 ---- ------ ---------- ---------- ----------- 

uj5u.com熱心網友回復：

可以通過多種方式執行此操作，但通常不受歡迎，但是因為您已經特別要求，STRING_SPLIT所以我為您提供了這種機制來決議字串的組件：

DECLARE @string2 varchar(max);
DECLARE @sep char(1);
set @string2 = 'DOB;Mar 1199;passport;AW1234567';
set @sep = ';'

SELECT ROW_NUMBER() OVER (order by (SELECT 1)) as RN, Value
FROM STRING_SPLIT(@string2, @sep, 1)

注意：通常建議不要使用這種hackORDER BY

來自檔案：輸出行可能是任何順序。不保證順序與輸入字串中子字串的順序相匹配

但是，如果您真的需要使用，您可以自行承擔風險 STRING_SPLIT

您現在可以通過它們的序號訪問這些令牌：

DECLARE @string2 varchar(max);
DECLARE @sep char(1);
set @string2 = 'DOB;Mar 1199;passport;AW1234567';
set @sep = ';'

;WITH Split as (
    SELECT ROW_NUMBER() OVER (order by (select 1)) as RN, Value
    FROM STRING_SPLIT(@string2, @sep)
)
SELECT s1.value as DOBKey, s2.value as DOBValue, s3.value as PassportKey, s4.value as PassportValue 
FROM Split as s1, Split as s2, Split as s3, Split as s4
WHERE s1.RN = 1 
  AND s2.RN = 2
  AND s3.RN = 3
  AND s4.RN = 4

結果如下：

SQL Azure 支持enable_ordinal標志

作為 SQL 2019 的更新并預計將很快發布，有一個enable_ordinal引數可用于確保字串標記的順序，它將在名為的輸出列中包含序ordinal數值。

但是，截至2021 enable_ordinal年 6 月 11 日，引數和ordinal輸出列目前僅在 Azure SQL 資料庫、Azure SQL 托管實體和 Azure Synapse Analytics（僅限無服務器 SQL 池）中受支持。

如果可用，我們可以簡化前面的查詢：

DECLARE @string2 varchar(max);
DECLARE @sep char(1);
set @string2 = 'DOB;Mar 1199;passport;AW1234567';
set @sep = ';'

;WITH Split as (
    SELECT Ordinal, Value
    FROM STRING_SPLIT(@string2, @sep, 1)
)
SELECT s1.Value as DOBKey, s2.Value as DOBValue, s3.Value as PassportKey, s4.Value as PassportValue 
FROM Split as s1, Split as s2, Split as s3, Split as s4
WHERE s1.Ordinal = 1 
  AND s2.Ordinal = 2
  AND s3.Ordinal = 3
  AND s4.Ordinal = 4

您可以使用相同的 CTE 來提取DOBValue和PassportValue中的INSERT：

DECLARE @string2 varchar(max);
DECLARE @sep char(1);
set @string2 = 'DOB;Mar 1199;passport;AW1234567';
set @sep = ';'

;WITH Split as (
    SELECT Ordinal, Value
    FROM STRING_SPLIT(@string2, @sep, 1)
)
insert into EMPLOYEE (dob1, pass1)
select s2.Value, s4.Value 
FROM Split as s1, Split as s2, Split as s3, Split as s4
WHERE s1.Ordinal = 1 
  AND s2.Ordinal = 2
  AND s3.Ordinal = 3
  AND s4.Ordinal = 4

應該導致：

標籤：

上一篇：針對結構不佳的API回應的OPENJSON()優化

下一篇：TSQL使用列A的值更新列B也正在更新