字元串和關系格式的轉化

在資料庫開發過程中，字元串和關系表的轉化是一項基本技能。當字元串中存在分隔符時，有時将其轉換成關系表資料，和其他資料表進行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?

作者

：悅光陰

出處

：http://www.cnblogs.com/ljhdo/

本文版權歸作者和部落格園所有，歡迎轉載，但未經作者同意，必須保留此段聲明，且在文章頁面醒目位置顯示原文連接配接，否則保留追究法律責任的權利。