Split text by space on SQL Server 2012

Question

I'm using SQL Server 2012, so no string split function available.

Consider that I have a table called DISK_VOLUME with only one column RESULT and currently 4 rows:

RESULT
----------------------------------------
P: 220825387008  959589646336  DADOS       
I: 166207356928  959589646336  INDEXS       
E: 636080054272  799165902848  LOG          
C: 462246113280  999651536896

What I want is a query that splits those rows by space, showing 4 columns like:

Caption  | Space 1      | Space 2        | Volume Name 
---------+--------------+----------------+--------------
P:       | 220825387008 | 959589646336   | DADOS 
I:       | 166207356928 | 959589646336   | INDEXS 
E:       | 636080054272 | 799165902848   | LOG 
C:       | 462246113280 | 999651536896   |

But there's no control of the amount of spaces in the string. It can have like

• Word space Word
• Word space space space space Word

Which means that using charindex(' ', ini_pos, ini_pos + 1) by itself won't work since I can't rely on having the next word right after the spaces lookup.

So basically what I was looking for is any function or combination of commands that splits a row by spaces dynamically (recursive function, xml, IDK). All my attempts on doing so have failed.

Answer 1

You can split the string using XML. You first need to convert the string to XML and replace the space with start and end XML tags.

Once the string is converted into XML, you can use XQuery to get the result in proper format.

To show the data in multiple column you can simply use PIVOT

;WITH CTE AS
(
 SELECT 
 F1.results,
 O.splitdata,
 row_number() over(partition by results order by (select 1)) rn FROM
    (
        SELECT *, 
        cast('<X>'+replace(F.results,' ','</X><X>')+'</X>' as XML) as xmlfilter 
        from [YourTableName] F
        )F1
        CROSS APPLY
        ( 
            SELECT fdata.D.value('.','varchar(500)') as splitdata 
            FROM f1.xmlfilter.nodes('X') as fdata(D) 
        ) O
        where splitdata <> ''
    ) 

 select [1] [Caption],[2] [Space1],[3] [Space2], [4] [Volume Name] 
 from CTE c
 PIVOT (max(splitdata) for rn in ([1],[2],[3],[4])) pvt

Online Demo

Output

+---------+--------------+--------------+-------------+
| Caption | Space1       | Space2       | Volume Name |
+---------+--------------+--------------+-------------+
| C:      | 462246113280 | 999651536896 | NULL        |
+---------+--------------+--------------+-------------+
| E:      | 636080054272 | 799165902848 | LOG         |
+---------+--------------+--------------+-------------+
| I:      | 166207356928 | 959589646336 | INDEXS      |
+---------+--------------+--------------+-------------+
| P:      | 220825387008 | 959589646336 | DADOS       |
+---------+--------------+--------------+-------------+