T-SQL 拆分使用钦定分隔符的字符串(split string)

比如有一个表,我们需要些一个语句像SELECT OtherID, SplitData WHERE SomeID
= ‘abcdef-…….’ , 然后就能返回分割成单独的行。

在数据库开发过程中,字符串和关系表的转化是一项基本技能。当字符串中存在分隔符时,有时将其转换成关系表数据,和其他数据表进行join查询,出现这种情况,是因为没有遵守关系数据库的设计范式,没有把字符串拆分成原子项存储,也有可能是数据传参数;有时会遇到相反的情况,需要将关系表的相关数据拼接成一个字符串显示,或传参。

原表:

把格式化的字符串转化成关系格式,基本思路分为两种:

| SomeID         | OtherID     | Data

  • 利用TSQL的循环语句:每一次循环都插入到关系表变量或临时表中,这种思路是面向过程的编程;
  • 使用XML查询:先把字符串转化成XML格式,再利用XML的nodes()函数,把XML数据转化成关系数据;这种思路是面向集合的编程,建议采用XML查询实现;

+—————-+————-+——————-

把关系格式转化成字符串,基本思路分为两种:

| abcdef-…..   | cdef123-… | 18,20,22

  • 利用TSQL的游标,对字符串执行累加连接,这种思路是面向过程的编程;
  • 利用XML查询的for xml
    path子句,把关系格式转化成字符串;这种思路是面向集合的编程,建议采用XML查询实现;

| abcdef-…..   | 4554a24-… | 17,19

一,将字符串转换成表

| 987654-…..   | 12324a2-… | 13,19,20

先把字符串转换成XML格式,再利用XML的nodes()函数,把XML数据转化成关系数据,这种实现方式性能快,代码简洁,

 

declare @separator varchar(10)
declare @str varchar(max)

set @separator=','
set @str='54,57,55,56,59'

预期结果:

1,把字符串转化成节点值

| OtherID     | SplitData

每个子串都是节点值,只需要取出节点值,就可以把节点值转化成关系格式的列值

+————-+——————-

declare @xml xml
set @xml=convert(xml,'<v>' + REPLACE(@str, @separator, '</v><v>') + '</v>')

SELECT ids=N.v.value('.', 'int') 
FROM @xml.nodes('/v') N(v)

| cdef123-… | 18

2,把字符串转化成节点属性

| cdef123-… | 20

每个子串都是节点的属性值,只需要取出节点的属性值,就可以把属性值转化成关系格式的列值

澳门金沙vip,| cdef123-… | 22

declare @xml xml
set @xml=convert(xml,'<Item v=''' + REPLACE(@str, @separator, '''></Item><Item v=''') + '''></Item>')

SELECT ids=N.v.value('@v', 'int') 
FROM @xml.nodes('/Item') N(v)

| 4554a24-… | 17

3,内置表值函数(string_split)