我正在使用 SQL Server 2014。我將這樣的地址作為一個大字串存盤在表中呼叫的列addr中tblAddress（我無法控制它來自第 3 方供應商）
addr
--------------------------------------------------
1 Smith Street;My Old Town; Someplace;Pluto;
5 SmokeyStreet;Someoldtown;Someotherplace;;
7 Grove Hill; Someolder town;Someotherplace;Earth
我想提取它，所以它看起來像下面分成 4 列：
Addr1 Addr2 Addr3 Addr4
----------------------------------------------------------
1 Smith Street My Old Town Someplace Pluto
5 SmokeyStreet Someoldtown Someotherplace
7 Grove Hill Someolder town Someotherplace Earth
我試過了：
SELECT
CAST('<x>' REPLACE([addr], ';', '</x><x>') '</x>' AS XML).value('/x[1]', 'nvarchar(50)') AS Addr1,
CAST('<x>' REPLACE([addr], ';', '</x><x>') '</x>' AS XML).value('/x[2]', 'nvarchar(50)') AS Addr2,
CAST('<x>' REPLACE([addr], ';', '</x><x>') '</x>' AS XML).value('/x[3]', 'nvarchar(50)') AS Addr3,
CAST('<x>' REPLACE([addr], ';', '</x><x>') '</x>' AS XML).value('/x[4]', 'nvarchar(50)') AS Addr4
FROM
tblAddress;
但我不斷收到錯誤訊息：
XML 決議：第 1 行，字符 49，非法名稱字符
我哪里錯了？
uj5u.com熱心網友回復：
與其重復計算，不如考慮使用一次 CROSS APPLY
例子
Select Pos1 = xDim.value('/x[1]' ,'varchar(150)')
,Pos2 = xDim.value('/x[2]' ,'varchar(150)')
,Pos3 = xDim.value('/x[3]' ,'varchar(150)')
,Pos4 = xDim.value('/x[4]' ,'varchar(150)')
From YourTable A
Cross Apply ( values (cast('<x>' replace((Select replace(A.addr,';','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>') '</x>' as xml)) )B(xDim)
結果
Pos1 Pos2 Pos3 Pos4
1 Smith Street My Old Town Someplace Pluto
5 SmokeyStreet Someoldtown Someotherplace
7 Grove Hill Someolder town Someotherplace Earth
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/466137.html
下一篇：SQL更新按特定日期排序的重復項