字元串和關係格式的轉化

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