SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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 *

FROM XX;

;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