使用 XML 的版本。
declare @S varchar(100) = 'Hello John Smith'
select
n.r.value('.', 'varchar(50)')
from (select cast('<r>'+replace(@S, ' ', '</r><r>')+'</r>' as xml)) as s(XMLCol)
cross apply s.XMLCol.nodes('r') as n(r)
使用表格代替
代替@T
与您使用的任何桌子一起使用。
-- Test table
declare @T table (ID int, Col varchar(100))
insert into @T values (1, 'Hello John Smith')
insert into @T values (2, 'xxx yyy zzz')
select
T.ID,
n.r.value('.', 'varchar(50)')
from @T as T
cross apply (select cast('<r>'+replace(replace(Col,'&','&'), ' ', '</r><r>')+'</r>' as xml)) as S(XMLCol)
cross apply S.XMLCol.nodes('r') as n(r)
分割字符串'Hello John Smith'
不使用变量
select
n.r.value('.', 'varchar(50)')
from (select cast('<r>'+replace('Hello John Smith', ' ', '</r><r>')+'</r>' as xml)) as s(XMLCol)
cross apply s.XMLCol.nodes('r') as n(r)