Split string values with various length spearated spaces into three columns

Question

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

Do the useful values actually fall into fixed-width columns, e.g. the first column is always 28 characters with right-padding? If so, you can use `Substring` and `RTrim` to extract the values and trim the padding without needing to worry about any "internal" spaces. — HABO, Feb 11 '20 at 04:34

Thom A · Answer 1 · 2020-02-11T14:16:39.703

STRING_SPLIT doesn't care about ordinal position, so isn't helpful here. I suggest grabbing a copy of DelimitedSplit8K_LEAD. Then you can split as below;

WITH Splits AS(
    SELECT V.YourColumn,
           DS.ItemNumber,
           DS.Item,
           ROW_NUMBER() OVER (PARTITION BY V.YourColumn ORDER BY DS.ItemNumber) AS RN
    FROM (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')) V (YourColumn)
         CROSS APPLY dbo.DelimitedSplit8K_LEAD(REPLACE(V.YourColumn, '  ', '|'), '|') DS
    WHERE DS.Item NOT IN ('',' '))
SELECT MAX(CASE S.RN WHEN 1 THEN TRIM(S.Item)END) AS Z1,
       MAX(CASE S.RN WHEN 2 THEN TRIM(S.Item)END) AS Z2,
       MAX(CASE S.RN WHEN 3 THEN TRIM(S.Item)END) AS Z3
FROM Splits S
GROUP BY S.YourColumn;
GO

If TRIM isn't a found function, use LTRIM and RTRIM.

Hi Lamu, Thanks for reponding to my post. Your code did not work because some indivual value has a space itself and separated with next value with variable length spaces. As stated in my example. Let me know. — Etex, Feb 11 '20 at 13:53

score 1 · Accepted Answer · answered Feb 10 '20 at 22:37

1

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

answered Feb 10 '20 at 22:37

John Cappelletti

79,615
7
44
66

Hi John, I tried your your syntax and I got an error of 'Msg 9420, Level 16, State 1, Line 219 XML parsing: line 1, character 9, illegal xml character'. I googled the error is something to do with the Unicode. My values do have unicode values. Do you know how to resolve it? Thanks – Etex Feb 11 '20 at 13:57
@Etex Perhaps change all varchar() to nvarchar(). Hard to say without seeing your actual data. – John Cappelletti Feb 11 '20 at 14:02
I replaced the varchar() to nvarchar() and I am getting the same error. Any ideas how to replacereplace the illegal characters? I am thinking those characters maybe >< &%^* / # . , on my values that caussing the error. I saw somepost suggested to place this in my code to fix the problem but where do I place it on your syntax? – Etex Feb 11 '20 at 14:52
@Etex I don't know what to say. As I suspected (and re-tested) the solution is XML Safe... meaning it will consume XML characters. take a peek at https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=cf0455da8a8880b5a34b563c3227884d You'll see that I added some non-safe characters – John Cappelletti Feb 11 '20 at 15:02
I fixed my problem by creating a function named.RemoveInvalidXMLCharacters to remove the illegal characters that I found in https://stackoverflow.com/questions/54693134/xml-parsing-illegal-character-in-sql-server. Thanks for your help. I added the function on your replace(RemoveInvalidXMLCharacters([Col]),' – Etex Feb 11 '20 at 15:18
@Etex Pleased you were able to resolve the issue. Well Done! – John Cappelletti Feb 11 '20 at 15:20
@Etex I now see where the issue was. There were control characters in your data, The >#&% were not the issue. – John Cappelletti Feb 11 '20 at 15:23
You mention controled characters.So is there a way to fix your code without using the function I created? – Etex Feb 11 '20 at 16:03
@Etex I have a function that strips control characters. I don't know if it would be more performant than the one you selected, but it has been received well. https://stackoverflow.com/questions/43148767/sql-server-remove-all-non-printable-ascii-characters/43149139#43149139 – John Cappelletti Feb 11 '20 at 16:14

Split string values with various length spearated spaces into three columns

2 Answers2