I'm using SQL Server 2014. I have addresses stored like this as one large string in a column called addr
in a table tblAddress
(I have no control over this it is from a 3rd party supplier)
addr
--------------------------------------------------
1 Smith Street;My Old Town; Someplace;Pluto;
5 SmokeyStreet;Someoldtown;Someotherplace;;
7 Grove Hill; Someolder town;Someotherplace;Earth
I would like to extract it so it looks like the below split into 4 columns:
Addr1 Addr2 Addr3 Addr4
----------------------------------------------------------
1 Smith Street My Old Town Someplace Pluto
5 SmokeyStreet Someoldtown Someotherplace
7 Grove Hill Someolder town Someotherplace Earth
I've tried:
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;
But I keep getting an error:
XML parsing: line 1, character 49, illegal name character
Where am I going wrong?
CodePudding user response:
Rather than repeating calculations, you should consider using a CROSS APPLY once
Example
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)
Results
Pos1 Pos2 Pos3 Pos4
1 Smith Street My Old Town Someplace Pluto
5 SmokeyStreet Someoldtown Someotherplace
7 Grove Hill Someolder town Someotherplace Earth