字符串和关系表的转化

发布时间：2017-07-18 10:15:01.223 作者： IT网络文摘 (该文来自笔记，点击查看原文)

在数据库开发过程中，字符串和关系表的转化是一项基本技能。当字符串中存在分隔符时，有时将其转换成关系表数据，和其他数据表进行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 xmlset @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 xmlset @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 descrfrom 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?

--业精于勤而荒于嬉，行成于思而毁于随--
--欢迎转载，转载请注明出处--

http://www.cnblogs.com/ljhdo/p/4789343.html

分类导航

字符串和关系表的转化

延伸阅读

我想了解如何学习