create table OneTomore
(
id int identity(1,1),
Name varchar(255)
)
;
insert into OneTomore values('1*2*3*4*5')
insert into OneTomore values('1*2*3*4*5')
insert into OneTomore values('1*2*3*4*5*****')
;
;with mycte as
(
SELECT
ID,
CAST('' + REPLACE(Name, '*', '') + '' AS XML) AS KID
FROM OneTomore
)
select ID,x.i.value('.', 'VARCHAR(10)') AS KID
from mycte
CROSS APPLY KID.nodes('i') x(i)
where len(x.i.value('.', 'VARCHAR(10)'))<>''
摘自 周尚冬的专栏