Bag Want

使用 charindex + CROSS APPLY 將資料單欄轉多欄

範例如下：

DECLARE @table table (CustDetail varchar(500))

insert into @table
select 'Name=Join&Age=23&Gender=男&City=台北市'
union all
select 'Name=Tom&Age=32&Gender=男&City=新北市'
union all
select 'Name=May&Age=18&Gender=女&City=台中市'
union all
select 'Name=Alen&Age=25&Gender=男&City=台中市'
union all
select 'Name=Ben&Age=18&Gender=男&City=新北市'

select CustDetail
, Substring(CustDetail, 0, charindex('&', CustDetail)) as Name
, substring(CustDetail, P1.Cust + 1, P2.Cust - P1.Cust - 1) as Age
, substring(CustDetail, P2.Cust + 1, P3.Cust - P2.Cust - 1) as Gender
, substring(CustDetail, P3.Cust + 1, LEN(CustDetail) - p3.Cust) as City
from @table
CROSS APPLY (select (charindex('&', CustDetail))) as P1(Cust)
CROSS APPLY (select (charindex('&', CustDetail, P1.Cust+1))) as P2(Cust)
CROSS APPLY (select (charindex('&', CustDetail, P2.Cust+1))) as P3(Cust)
CROSS APPLY (select (charindex('&', CustDetail, P3.Cust+1))) as P4(Cust)