原型数据:
'HY-107860;HY-107871;HY-109816;HY-109824;HY-109826;HY-113868;HY-2169'
需求:
按分号将字符串分割为多行,填充下拉列表
SQL实现脚本
SELECT b.T
FROM(
SELECT CONVERT(XML,'<n>'+REPLACE(REPLACE('HY-107860;HY-107871;HY-109816;HY-109824;HY-109826;HY-113868;HY-2169',';',','),',','</n><n>')+'</n>') AS xmlval
) a
CROSS APPLY(
SELECT k.n.value('.','nvarchar(80)') T
FROM a.xmlval.nodes('n') k(n)
) b
输出结果:
基本原理:先将该字段值统一替换为分号分割,再将分号分割替换转为XML数据类型,再利用xml转为多个行
XML非法字符的处理
在解释XML时,会因为一些非法字符解析异常,因此在解析XML前处理非法字符十分重要。
XML的非法字符包括:
1.需去除去的非法字符范围,在W3C手册XML的非法字符可以查找到:
\\x00-\\x08
\\x0b-\\x0c
\\x0e-\\x1f
2.需要替换的字符:
字符 | HTML字符 | 字符编码 |
---|---|---|
和 & | & | & |
单引号 ’ | ' | ' |
双引号 ” | " | " |
大于号 > | > | > |
小于号 < | < | < |
select
cast('<n>' +
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
'HY-107860&HY-107871;HY-109816;HY-109824;HY-109826;HY-113868;HY-2169'
,';','!')
,char(31),'')
,char(14),'')
,char(12),'')
,char(11),'')
,char(8),'')
,char(0),'')
,'<','<')
,'>','>')
,'"','"')
,'''',''')
,'&','&')
, '!', '</n><n>')
+ '</n>' AS XML) AS [DrugNames]
into #A
select ltrim(rtrim(x.i.value('.', 'NVARCHAR(max)'))) AS [T]
from #A
CROSS APPLY [DrugNames].nodes('n') x(i)
where len(x.i.value('.', 'NVARCHAR(max)')) <> ''
drop table #A