如何使用SQL自定義字元串分割函數

一、什麼是字元串分割函數

在SQL中，字元串分割函數是一種將字元串按照指定分隔符分割成多個子字元串的函數。它通常用於將一個列中包含多個值的記錄分解成單個值的多條記錄。例如，一個地址列中包含了省、市、區等多個信息，我們可以使用字元串分割函數將其分解成單獨的省、市、區列。

二、SQL內置的字元串分割函數

標準的SQL語言中並沒有提供字元串分割函數，但是不同廠商的資料庫中通常提供了自己的解決方案。例如，MySQL提供了SUBSTRING_INDEX函數，Oracle提供了REGEXP_SUBSTR函數，SQL Server提供了PARSENAME函數。

-- MySQL的SUBSTRING_INDEX函數示例
SELECT SUBSTRING_INDEX('www.google.com', '.', 1) AS first_part, 
       SUBSTRING_INDEX(SUBSTRING_INDEX('www.google.com', '.', 2), '.', -1) AS second_part,
       SUBSTRING_INDEX('www.google.com', '.', -1) AS third_part;
-- 輸出結果：first_part: 'www', second_part: 'google', third_part: 'com'

三、自定義字元串分割函數

對於那些不提供字元串分割函數的資料庫，我們可以使用SQL語言自帶的函數和語法來編寫自定義的字元串分割函數。

四、使用遞歸查詢實現字元串分割函數

遞歸查詢是一種SQL查詢技術，可以在一個查詢中多次調用自己。我們可以使用遞歸查詢來實現字元串分割函數。

CREATE FUNCTION str_split(s VARCHAR(8000), delim CHAR(1))
RETURNS @result TABLE (ret VARCHAR(8000))
AS
BEGIN
    DECLARE @pos INT
    WHILE CHARINDEX(delim, s) > 0
    BEGIN
        SET @pos = CHARINDEX(delim, s)
        INSERT INTO @result(ret) VALUES (SUBSTRING(s, 1, @pos-1))
        SET s = SUBSTRING(s, @pos+1, LEN(s)-@pos)
    END
    INSERT INTO @result(ret) VALUES (s)
    RETURN
END

使用方法：

SELECT * FROM dbo.str_split('1,2,3,4,5', ',')
-- 輸出結果：ret: '1', '2', '3', '4', '5'

五、使用CTE實現字元串分割函數

CTE（Common Table Expression）是SQL語言的一種代碼結構，可以將查詢的結果作為表格一樣的數據進行處理。我們可以使用CTE來實現字元串分割函數。

CREATE FUNCTION str_split_cte(s VARCHAR(8000), delim CHAR(1))
RETURNS @result TABLE (ret VARCHAR(8000))
AS
BEGIN
    WITH Split(st_pos, end_pos) AS
    (
        SELECT 0 st_pos, CHARINDEX(delim, s) end_pos
        UNION ALL
        SELECT end_pos+1, CHARINDEX(delim, s, end_pos+1)
        FROM Split
        WHERE end_pos > 0
    )
    INSERT INTO @result(ret)
    SELECT SUBSTRING(s, st_pos+1, CASE WHEN end_pos>0 THEN end_pos ELSE LEN(s)+1 END - st_pos-1)
    FROM Split
    OPTION(MAXRECURSION 0)
    RETURN
END

使用方法：

SELECT * FROM dbo.str_split_cte('1,2,3,4,5', ',')
-- 輸出結果：ret: '1', '2', '3', '4', '5'

六、使用XML實現字元串分割函數

XML是一種用於在不同應用程序之間共享數據的通用格式。我們可以使用XML來實現字元串分割函數。

CREATE FUNCTION str_split_xml(s VARCHAR(8000), delim CHAR(1))
RETURNS @result TABLE (ret VARCHAR(8000))
AS
BEGIN
    DECLARE @xml_string XML
    SET @xml_string = '' + REPLACE(s, delim, '') + ''
    INSERT INTO @result(ret)
    SELECT t.x.value('.', 'VARCHAR(8000)')
    FROM @xml_string.nodes('/StringSplit/value') t(x)
    RETURN
END

使用方法：

SELECT * FROM dbo.str_split_xml('1,2,3,4,5', ',')
-- 輸出結果：ret: '1', '2', '3', '4', '5'