在一个表里面,字段 mailto ,内容是: [email protected];[email protected],[email protected];[email protected]
这样的字符,中间用逗号或分号隔开,如何把它变成记录集:
[email protected]
[email protected]
[email protected]
[email protected]
这样的SQL要如何写,谢谢!
这样的字符,中间用逗号或分号隔开,如何把它变成记录集:
[email protected]
[email protected]
[email protected]
[email protected]
这样的SQL要如何写,谢谢!
set @mailto='[email protected];[email protected],[email protected];[email protected]'
set @xml=convert(xml,'<x>'+replace(replace(@mailto,';','</x><x>'),',','</x><x>')+'</x>')
select n.v.value('.','varchar(50)') as email from @xml.nodes('/x') as n(v)
/*
email
--------------------------------------------------
[email protected]
[email protected]
[email protected]
[email protected]