字符串和关系格式的转化

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

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

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

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

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

一,将字符串转换成表

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

declare @separator varchar(10)
declare @str varchar(max)
    
set @separator=','
set @str='54,57,55,56,59'

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

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

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)

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

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

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)

3,内置表值函数(string_split)

SQL Server 2016 新增一个表值函数string_split,用于按照分隔符将字符串分割成表值数据,返回的字段名是Value

STRING_SPLIT ( string , separator )  

二,将表数据拼接成字符串

有以下数据表,有两列:ID和txt,ID值有重复,而txt是文本数据;

create table dbo.test
(
ID int,
txt varchar(10)
)

把ID字段相同的txt字段的值拼接成字符串显示

select ID
    ,(select a.txt+'' from dbo.test a where a.ID=t.ID for xml path('')) as descr
from dbo.test t 
group by ID

三,奇巧淫技

在master数据库中,存在一个系统视图:master.dbo.spt_values,该视图包含从0到2047的所有数字,利用这个特性,可以把特定长度的字符串转化成关系格式,实现的代码如下:

;with cte_numbers as 
(
    select number
    from master.dbo.spt_values
    where type='p'
        and number>0
)
select 
    cast(substring(@str, n.number, charindex(@separator ,@str +@separator ,n.number )-n.number)  as  nvarchar(4000)) as item 
    --,n.number
 from cte_numbers n
 where n.number<=len(@str)+1 
    and charindex(@separator,@separator+@str,n.number)=n.number

对于该方法,要体会其代码的思路,通过数据序列,从数字1开始,逐个检测分隔符,对字符串进行分割操作,截取子字符串,从而把字符串转化成关系表;由于master.dbo.spt_values只有0到2047个顺序数字,在必要时,可以替换该系统视图,而使用自定义的数据序列表,以增加能够拆分的字符串长度。

在数据库开发中,实现字符串和关系格式的相互转化,我倾向于使用面向集合的查询,通过面向过程的编程思想来实现,思路直接,比较简单,在此就不再赘述了。

 

参考文档:

Why (and how) to split column using master..spt_values?

posted @ 2017-07-18 10:07  悦光阴  阅读(...)  评论(... 编辑 收藏