在數(shù)據(jù)庫開發(fā)過程中,字符串和關(guān)系表的轉(zhuǎn)化是一項基本技能。當字符串中存在分隔符時,有時將其轉(zhuǎn)換成關(guān)系表數(shù)據(jù),和其他數(shù)據(jù)表進行join查詢,出現(xiàn)這種情況,是因為沒有遵守關(guān)系數(shù)據(jù)庫的設(shè)計范式,沒有把字符串拆分成原子項存儲,也有可能是數(shù)據(jù)傳參數(shù);有時會遇到相反的情況,需要將關(guān)系表的相關(guān)數(shù)據(jù)拼接成一個字符串顯示,或傳參。
把格式化的字符串轉(zhuǎn)化成關(guān)系格式,基本思路分為兩種:
利用TSQL的循環(huán)語句:每一次循環(huán)都插入到關(guān)系表變量或臨時表中,這種思路是面向過程的編程;
使用XML查詢:先把字符串轉(zhuǎn)化成XML格式,再利用XML的nodes()函數(shù),把XML數(shù)據(jù)轉(zhuǎn)化成關(guān)系數(shù)據(jù);這種思路是面向集合的編程,建議采用XML查詢實現(xiàn);
把關(guān)系格式轉(zhuǎn)化成字符串,基本思路分為兩種:
利用TSQL的游標,對字符串執(zhí)行累加連接,這種思路是面向過程的編程;
利用XML查詢的for xml path子句,把關(guān)系格式轉(zhuǎn)化成字符串;這種思路是面向集合的編程,建議采用XML查詢實現(xiàn);
一,將字符串轉(zhuǎn)換成表
先把字符串轉(zhuǎn)換成XML格式,再利用XML的nodes()函數(shù),把XML數(shù)據(jù)轉(zhuǎn)化成關(guān)系數(shù)據(jù),這種實現(xiàn)方式性能快,代碼簡潔,
declare @separator varchar(10)declare @str varchar(max) set @separator=','set @str='54,57,55,56,59'
1,把字符串轉(zhuǎn)化成節(jié)點值
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é)點屬性
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'