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)'))<>''

摘自 周尚冬的专栏