My tableA col1 has three values and separated by various length. I need to separate it and into three columns and place it into Z1, Z2, and Z3 respectively. Also, on some individual value has spaces in between too for example ‘Windows XP Professional’ but I need it make it as individual value. Any ideas? See sample below.Thanks.
Current
Col Z1 Z2 Z3 -------------------------------------------------------------------------- ----- ----- ---- Windows XP Professional SP3 CD RWT-00543 Windows XP N/A N/A Windows CE .NET v5.00 CD TRE-00298 in vault Client Ctrl firmware N/A
After
Col Z1 Z2 Z3
------------------------------------ ------------- ----- ----
Windows XP Professional SP3 PP RWT-00543
Windows XP N/A N/A
Windows CE .NET v5.00 QQ TRE-00298 in vault
Client Ctrl firmware N/A
Advertisement
Answer
Assuming there are at least TWO spaces between each “column”.
Cross Apply B will create a “clean” pipe delimited string” (Thanks Gordon!)
Cross Apply C will parse the new pipe delimited string into 3 columns
Example
Declare @YourTable Table ([Col] varchar(500))
Insert Into @YourTable Values
('Windows XP Professional SP3 CD RWT-00543')
,('Windows XP N/A N/A')
,('Windows CE .NET v5.00 CD TRE-00298 in vault')
,('Client Ctrl firmware N/A')
Select C.*
From @YourTable A
Cross Apply ( values ( ltrim(rtrim(replace(replace(replace([Col],' ','†‡'),'‡†',''),'†‡','|'))) ) ) B(CleanString)
Cross Apply (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
From ( values (cast('<x>' + replace((Select replace(CleanString,'|','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml))) A(xDim)
) C
Returns
Pos1 Pos2 Pos3 Windows XP Professional SP3 CD RWT-00543 Windows XP N/A N/A Windows CE .NET v5.00 CD TRE-00298 in vault Client Ctrl firmware N/A NULL