T-SQL——將字串轉換為多列

目錄

• 0. 使用ParseName
• 1. 具體到拆分為兩列
• 2. 具體到拆分的指定的列數

志銘-2023年2月21日

---

0. 使用ParseName

• 注意：ParseName最多隻能拆分為四列
• ParseName只能針對.，所以若是其他的分隔字元，需要先替換為.

WITH temp
AS (SELECT *
    FROM
    (
        VALUES
            ('192.168.3.239'),
            ('192.168.3.232')
    ) AS T (IP) )
SELECT IP,
       PARSENAME(IP, 1) AS IP1,
       PARSENAME(IP, 2) AS IP2,
       PARSENAME(IP, 3) AS IP3,
       PARSENAME(IP, 4) AS IP4
FROM temp;

IP                IP1 	IP2    IP3     IP4
--------------    ---   ---    ---     ---
192.168.3.239	  239 	3      168     192
192.168.3.232	  232 	3      168     192

---

1. 具體到拆分為兩列

WITH temp
AS (SELECT *
    FROM
    (
        VALUES
            ('1:2'),
            ('2:3')
    ) AS T (Ratio) )
SELECT Ratio,
       LEFT(Ratio, CHARINDEX(':', Ratio) - 1) AS R1,
       RIGHT(Ratio, (LEN(Ratio) - CHARINDEX(':', Ratio))) AS R2
FROM temp;

Ratio R1   R2
----- ---- ----
1:2   1    2
2:3   2    3

---

2. 具體到拆分的指定的列數

-- ======================================================
-- Author:		shanzm
-- Create date: 2020年5月5日 17:01:40
-- Description:	將指定的字串按照指定的分裂符分裂並返回指定
--              位置的值
-- ======================================================
CREATE FUNCTION [dbo].[f_GetSplitByIndex]
(
    @str VARCHAR(8000),  --包含多個資料項的字串
    @index INT,          --要獲取的資料項的位置
    @seprate VARCHAR(10) --資料分隔符
)
RETURNS VARCHAR(1000)
AS
BEGIN
    IF @str IS NULL
        RETURN (NULL);
    DECLARE @splitlen INT;
    SELECT @splitlen = LEN(@seprate + 'a') - 2;
    WHILE @index > 1 AND CHARINDEX(@seprate, @str + @seprate) > 0
    SELECT @index = @index - 1,
           @str  = STUFF(@str, 1, CHARINDEX(@seprate, @str + @seprate) + @splitlen, '');
    RETURN (ISNULL(LEFT(@str, CHARINDEX(@seprate, @str + @seprate) - 1), ''));
END;

WITH temp
AS (SELECT '張三，李四，王五' AS A)
SELECT A,
       dbo.f_GetSplitByIndex(A, 1, ',') AS A1,
       dbo.f_GetSplitByIndex(A, 2, ',') AS A2,
       dbo.f_GetSplitByIndex(A, 3, ',') AS A3
FROM temp;


--結果:
      A	            A1	    A2	    A3
---------------   ------  ------  ------
張三，李四，王五	   張三	   李四	   王五