T-SQL——將字串轉為單列

shanzm發表於2023-02-25
SQL字串

目錄

shanzm-2023年2月22日

0. 背景

程式碼中執行儲存過程，引數是多個且不確定數量，期望SQL查詢時使用該引數作為IN的篩選條件

比如說，具體引數@Ids="1,2,3,4",

期望在儲存過程中，實現 select * from Table where id In @Ids

直接這樣寫會報錯

（當然可以使用動態sql 進行拼接，但不需要這麼做），而是將傳遞的引數分裂為單列的行記錄！


1. 使用STRING_SPLIT函式

  • Sql Server在2016版本中支援使用STRING_SPLIT函式

    • 可以將字串按照分隔符，切割成一個資料表

  • 若是低版本資料使用提示物件名 'STRING_SPLIT' 無效。

SELECT * FROM STRING_SPLIT('1，2，3，4，5', ',');

--結果：
value
-------------
1
2
3
4
5

2. 自定義分裂函式

  • 實現方式1:基於字串操作

    • 將目標字串末尾拼接上一個分隔符
    • 從字串第一個位置開始查詢分隔符在字串中第一次出現的位置索引
    • 從左擷取（第一個分隔符索引-1）長度的字串，此外分裂出的第一個結果
    • 將目標字串從第一個分隔符之前的替換為空
    • 迴圈上述2~4步驟
-- ======================================================
-- Author:		shanzm
-- Create date: 2021年6月30日 15:52:02
-- Description:	將指定字串按照指定的分裂符分裂為單列表            
-- ======================================================
ALTER FUNCTION [dbo].[funGetSplitStr]
(
    @Str VARCHAR(8000),       --目標字串，形如"a,b,c"
    @StrSeprate VARCHAR(1)    --分隔符,形如","
)
RETURNS @temp TABLE           --返回表值變數，只有一列F1
(
    F1 VARCHAR(100)
)
AS
BEGIN
    DECLARE @ch AS VARCHAR(100);
    SET @Str = @Str + @StrSeprate;
    WHILE (@Str <> '')
    BEGIN
        SET @ch = LEFT(@Str, CHARINDEX(@StrSeprate, @Str, 1) - 1);
        INSERT @temp
        VALUES
        (@ch);
        SET @Str = STUFF(@Str, 1, CHARINDEX(@StrSeprate, @Str, 1), '');
    END;
    RETURN;
END;
  • 實現方式2:基於XML
-- ======================================================
-- Author:		shanzm
-- Create date: 2021年6月30日 15:52:02
-- Description:	將指定字串按照指定的分裂符分裂為單列表            
-- ======================================================
CREATE FUNCTION dbo.funGetSplitStr2
(
    @str varchar(1000),
    @strSperate varchar(10)
)
RETURNS @tableVar TABLE
(
    F1 VARCHAR(100)
)
AS
BEGIN
    DECLARE @xmlstr XML;
    --SET ARITHABORT ON;
    SET @xmlstr = CONVERT(XML, '<root><v>' + REPLACE(@str, @strSperate, '</v><v>') + '</v></root>');
    --SELECT @xmlstr;

    INSERT INTO @tableVar
    SELECT F1 = N.v.value('.', 'varchar(100)') FROM @xmlstr.nodes('/root/v') N(v);
	RETURN;
END;
GO


--測試
SELECT * FROM  funGetSpliterStr2('1.2.3','.')

--結果

F1
---------
1
2
3

(3 行受影響)

3. 使用示例

程式碼中傳遞的引數@Ids="1,2,3,4"，執行儲存過程作為篩選條件

這裡任意使用一個測試表Company，該表有一個Id欄位，儲存過程簡單的演示了Ids字串進行查詢

  • 建立測試儲存過程
CREATE PROCEDURE [dbo].[proTest]
@Ids VARCHAR(500)
AS
BEGIN
    SELECT *
    FROM dbo.Company
    WHERE Id IN
          (
              SELECT F1 FROM dbo.funGetSplitStr(@Ids, ',')
          );
END;


EXEC dbo.proTest @Ids = '1,3';

相關文章