[MSSQL]欄位拆解相關疑問

ms sql 欄位切割

希希寶 2022-04-05 14:06:31 ‧ 1480 瀏覽

分享至

原始資料如下圖

我希望先將「編號」欄位內容用「，」切割，並保留其他欄位相同內容，如下圖

最後再將各「編號」欄位以「-」切割成三欄，如下圖

想詢問在MSSQL內，該下那些語法才能達到最後的效果？
是否能一次轉換成功，還是必須分開先用逗號區隔後再使用-切割？
謝謝各位！

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

rogeryao

iT邦超人 7 級 ‧ 2022-04-05 15:38:54

最佳解答

CREATE TABLE XX (
X1 NVARCHAR(50) NULL,
X2 NVARCHAR(50) NULL,
X3 NVARCHAR(50) NULL,
X4 NVARCHAR(50) NULL,
X5 NVARCHAR(50) NULL);

INSERT INTO XX (X1,X2,X3,X4,X5) VALUES
(N'王明明',N'男','0985124793',N'新北市','X200-35-02,R205-10-65'),
(N'陳咚咚',N'男','0945137685',N'高雄市','X203-35-02'),
(N'李亮亮',N'女','0946825103',N'新竹市','X405-45-02,L210-11-07,R305-97-15'),
(N'吳花花',N'女','0978456285',N'台北市','X200-35-02'),
(N'沈芊芊',N'女','0956721854',N'基隆市','R205-10-69'),
(N'潘圈圈',N'女','0956123456',N'宜蘭市','');

SELECT D.X1,D.X2,D.X3,D.X4,
D.vals.value('(/TR/TD)[1]','VARCHAR(20)') AS NO1,
D.vals.value('(/TR/TD)[2]','VARCHAR(20)') AS NO2,
D.vals.value('(/TR/TD)[3]','VARCHAR(20)') AS NO3
FROM (
SELECT *,
CAST('<TR><TD>' + REPLACE(X5,'-', '</TD><TD>') + '</TD></TR>' AS xml) vals
FROM (
SELECT A.X1,A.X2,A.X3,A.X4,B.value AS X5
FROM XX AS A
CROSS APPLY STRING_SPLIT(A.X5, ',') AS B
) AS C) AS D
ORDER BY D.X1,NO1,NO2,NO3

Demo

SQL Server 2016 以前不支援 STRING_SPLIT :

;WITH CTE_X0 AS (
SELECT X1,X2,X3,X4,X5 + ',' AS X5
FROM XX),
CTE_X1 AS (
SELECT X1,X2,X3,X4,X5,
SUBSTRING(X5,1,CHARINDEX(',', X5) - 1) AS STRA,
SUBSTRING(X5,CHARINDEX(',', X5) + 1,LEN(X5) - CHARINDEX(',', X5)) AS STRB,
CHARINDEX(',', X5) AS NUM
FROM CTE_X0
--
UNION ALL
SELECT X1,X2,X3,X4,X5,
SUBSTRING(STRB,1,CHARINDEX(',', STRB) - 1) AS STRA,
SUBSTRING(STRB,CHARINDEX(',', STRB) + 1 ,LEN(STRB) - CHARINDEX(',', STRB)) AS STRB,
NUM + CHARINDEX(',', STRB) AS NUM
FROM CTE_X1
WHERE LEN(X5) - NUM > 0
)

SELECT D.X1,D.X2,D.X3,D.X4,
D.vals.value('(/TR/TD)[1]','VARCHAR(20)') AS NO1,
D.vals.value('(/TR/TD)[2]','VARCHAR(20)') AS NO2,
D.vals.value('(/TR/TD)[3]','VARCHAR(20)') AS NO3
FROM (
SELECT A.X1,A.X2,A.X3,A.X4,
CAST('<TR><TD>' + REPLACE(A.STRA,'-', '</TD><TD>') + '</TD></TR>' AS xml) vals
FROM CTE_X1 AS A
) AS D
ORDER BY D.X1,NO1,NO2,NO3

Demo

回應 3
分享
檢舉

海綿寶寶 iT邦大神 1 級 ‧ 2022-04-05 17:41:03 檢舉

whitefloor iT邦研究生 2 級 ‧ 2022-04-05 20:23:16 檢舉

太猛了...

希希寶 iT邦新手 4 級 ‧ 2022-04-05 22:32:35 檢舉

因為我是使用2014，剛剛正在研究STRING_SPLIT的語法，後面的語法成功解決了我的問題，感謝大大！

登入發表回應

海綿寶寶

iT邦大神 1 級 ‧ 2022-04-05 14:26:51

如果你之後還會問 SQL 問題
先到db fiddle建立 table 及資料
再提供在這裡
相信會很快得到你要的答案

回應 1
分享
檢舉

希希寶 iT邦新手 4 級 ‧ 2022-04-05 22:31:17 檢舉

原來還有這個工具，感謝大大！

登入發表回應

一級屠豬士

iT邦大師 1 級 ‧ 2022-04-05 18:45:32

MySQL 8 有新的功能 json_table
https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

這個例子可以應用

create table it020405 (
  iname varchar(10)
, isex char(1)
, itel varchar(10)
, iaddr varchar(10)
, ino varchar(50)
);

insert into it020405 values
('王明明','男','0985124793','新北市','X200-35-02,R205-10-65'),
('陳咚咚','男','0945137685','高雄市','X203-35-02'),
('李亮亮','女','0946825103','新竹市','X405-45-02,L210-11-07,R305-97-15'),
('吳花花','女','0978456285','台北市','X200-35-02'),
('沈芊芊','女','0956721854','基隆市','R205-10-69');

select iname, isex, itel, iaddr
     , substring_index(sno, '-', 1) as ino1
     , substring_index(substring_index(sno,'-',-2),'-',1) as ino2
     , substring_index(substring_index(sno,'-',-1),'-',1) as ino3
  from (select i.iname, isex, itel, iaddr
             , x.sno
          from it020405 i
          join json_table(
                 replace(json_array(i.ino), ',', '","')
               , '$[*]' columns (sno varchar(10) path '$')
               ) x
        ) y;
        
+-----------+------+------------+-----------+------+------+------+
| iname     | isex | itel       | iaddr     | ino1 | ino2 | ino3 |
+-----------+------+------------+-----------+------+------+------+
| 王明明    | 男   | 0985124793 | 新北市    | X200 | 35   | 02   |
| 王明明    | 男   | 0985124793 | 新北市    | R205 | 10   | 65   |
| 陳咚咚    | 男   | 0945137685 | 高雄市    | X203 | 35   | 02   |
| 李亮亮    | 女   | 0946825103 | 新竹市    | X405 | 45   | 02   |
| 李亮亮    | 女   | 0946825103 | 新竹市    | L210 | 11   | 07   |
| 李亮亮    | 女   | 0946825103 | 新竹市    | R305 | 97   | 15   |
| 吳花花    | 女   | 0978456285 | 台北市    | X200 | 35   | 02   |
| 沈芊芊    | 女   | 0956721854 | 基隆市    | R205 | 10   | 69   |
+-----------+------+------------+-----------+------+------+------+

8 rows in set (0.00 sec)

Demo

PG部分,在PG14 有新的函數 string_to_table().
之前的版本可以使用 unnest(string_to_array()) 搭配使用.

select iname, isex, itel, iaddr
     , split_part(sno, '-', 1) as ino1
     , split_part(sno, '-', 2) as ino2
     , split_part(sno, '-', 3) as ino3
  from (select iname, isex, itel, iaddr
             , string_to_table(ino, ',') as sno
         from it020405
       ) a;

PG_Demo