在數(shù)據(jù)庫(kù)開(kāi)發(fā)過(guò)程中，字符串和關(guān)系表的轉(zhuǎn)化是一項(xiàng)基本技能。當(dāng)字符串中存在分隔符時(shí)，有時(shí)將其轉(zhuǎn)換成關(guān)系表數(shù)據(jù)，和其他數(shù)據(jù)表進(jìn)行join查詢，出現(xiàn)這種情況，是因?yàn)闆](méi)有遵守關(guān)系數(shù)據(jù)庫(kù)的設(shè)計(jì)范式，沒(méi)有把字符串拆分成原子項(xiàng)存儲(chǔ)，也有可能是數(shù)據(jù)傳參數(shù)；有時(shí)會(huì)遇到相反的情況，需要將關(guān)系表的相關(guān)數(shù)據(jù)拼接成一個(gè)字符串顯示，或傳參。

把格式化的字符串轉(zhuǎn)化成關(guān)系格式，基本思路分為兩種：

  • 利用TSQL的循環(huán)語(yǔ)句：每一次循環(huán)都插入到關(guān)系表變量或臨時(shí)表中，這種思路是面向過(guò)程的編程；

  • 使用XML查詢：先把字符串轉(zhuǎn)化成XML格式，再利用XML的nodes()函數(shù)，把XML數(shù)據(jù)轉(zhuǎn)化成關(guān)系數(shù)據(jù)；這種思路是面向集合的編程，建議采用XML查詢實(shí)現(xiàn)；

把關(guān)系格式轉(zhuǎn)化成字符串，基本思路分為兩種：

  • 利用TSQL的游標(biāo)，對(duì)字符串執(zhí)行累加連接，這種思路是面向過(guò)程的編程；

  • 利用XML查詢的for xml path子句，把關(guān)系格式轉(zhuǎn)化成字符串；這種思路是面向集合的編程，建議采用XML查詢實(shí)現(xiàn)；

一，將字符串轉(zhuǎn)換成表

先把字符串轉(zhuǎn)換成XML格式，再利用XML的nodes()函數(shù)，把XML數(shù)據(jù)轉(zhuǎn)化成關(guān)系數(shù)據(jù)，這種實(shí)現(xiàn)方式性能快，代碼簡(jiǎn)潔，

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

1，把字符串轉(zhuǎn)化成節(jié)點(diǎn)值

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，把字符串轉(zhuǎn)化成節(jié)點(diǎn)屬性

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，內(nèi)置表值函數(shù)(string_split)

SQL Server 2016 新增一個(gè)表值函數(shù)string_split，用于按照分隔符將字符串分割成表值數(shù)據(jù)，返回的字段名是Value

STRING_SPLIT ( string , separator )

二，將表數(shù)據(jù)拼接成字符串

有以下數(shù)據(jù)表，有兩列：ID和txt，ID值有重復(fù)，而txt是文本數(shù)據(jù)；

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數(shù)據(jù)庫(kù)中，存在一個(gè)系統(tǒng)視圖：master.dbo.spt_values，該視圖包含從0到2047的所有數(shù)字，利用這個(gè)特性，可以把特定長(zhǎng)度的字符串轉(zhuǎn)化成關(guān)系格式，實(shí)現(xiàn)的代碼如下：

平面設(shè)計(jì)培訓(xùn),網(wǎng)頁(yè)設(shè)計(jì)培訓(xùn),美工培訓(xùn),游戲開(kāi)發(fā),動(dòng)畫(huà)培訓(xùn)

;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

平面設(shè)計(jì)培訓(xùn),網(wǎng)頁(yè)設(shè)計(jì)培訓(xùn),美工培訓(xùn),游戲開(kāi)發(fā),動(dòng)畫(huà)培訓(xùn)

對(duì)于該方法，要體會(huì)其代碼的思路，通過(guò)數(shù)據(jù)序列，從數(shù)字1開(kāi)始，逐個(gè)檢測(cè)分隔符，對(duì)字符串進(jìn)行分割操作，由于master.dbo.spt_values只有0到2047個(gè)順序數(shù)字，在必要時(shí)，可以替換該系統(tǒng)視圖，而使用自定義的數(shù)據(jù)序列表，以增加能夠拆分的字符串長(zhǎng)度。

參考文檔：

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

--業(yè)精于勤而荒于嬉，行成于思而毀于隨--
--歡迎轉(zhuǎn)載，轉(zhuǎn)載請(qǐng)注明出處--

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