Split text in SQL Server 2008

Question

I have column which has values like this

"BEAAL - Aalter - Belgium - EMEA"

I want to create a table where I want to split it in to multiple columns

Location = BEAAL - Aalter
Country = Belgium
Region = EMEA

So my table should look like this

'ID'   'Text'                    'Location'               'Country'    'Region' 
'1 '   'BEAAL - Aalter'          'BEAAL - Aalter'         'Belgium'    'EMEA'

How can this be achieved in SQL Server 2008? I have around 1000 entries in table to split this way.

Nothing available when you search for 'sql server 2008 split text'? — dfundako, Nov 09 '16 at 19:08
It's fairly easy if the structure is fixed (meaning the first and second parts are always location, third is always country and forth is always region). Look for string split in sql server documentation. — Zohar Peled, Nov 09 '16 at 19:13

score 0 · Answer 1 · edited May 23 '17 at 12:13

This script produces what you want, using a slightly modified version of the SplitStrings function you can find in this answer. All credit to Aaron Bertrand for that function.

Modified SplitStrings function:

CREATE FUNCTION [dbo].[SplitStrings](@List NVARCHAR(MAX))
RETURNS TABLE
AS
   RETURN ( SELECT Item=LTRIM(RTRIM(Item)),Id FROM
       ( SELECT Item = x.i.value('(./text())[1]', 'nvarchar(max)'),Id=x.i.value('for $i in . return count(../*[.<<$i])+1','INT')
         FROM ( SELECT [XML] = CONVERT(XML, '<i>'
         + REPLACE(@List, '-', '</i><i>') + '</i>').query('.')
           ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
       WHERE Item IS NOT NULL
   );

The script:

DECLARE @t TABLE(id int identity(1,1), blabla VARCHAR(512));
INSERT INTO @t(blabla)VALUES('BEAAL - Aalter - Belgium - EMEA');
INSERT INTO @t(blabla)VALUES('YYYY - Gent - Belgium - XXXX');
INSERT INTO @t(blabla)VALUES('ZZZZZ - Brussel - Belgium - WWWWW');

SELECT 
    t.id,
    Location=MAX(CASE WHEN split.Id=1 THEN split.Item END)+' - '+MAX(CASE WHEN split.Id=2 THEN split.Item END),
    Country=MAX(CASE WHEN split.Id=3 THEN split.Item END),
    Region=MAX(CASE WHEN split.Id=4 THEN split.Item END)
FROM 
    @t AS t
    CROSS APPLY dbo.SplitStrings(blabla) AS split
GROUP BY
    t.Id;

The result:

╔════╦═════════════════╦═════════╦════════╗
║ id ║    Location     ║ Country ║ Region ║
╠════╬═════════════════╬═════════╬════════╣
║  1 ║ BEAAL - Aalter  ║ Belgium ║ EMEA   ║
║  2 ║ YYYY - Gent     ║ Belgium ║ XXXX   ║
║  3 ║ ZZZZZ - Brussel ║ Belgium ║ WWWWW  ║
╚════╩═════════════════╩═════════╩════════╝

Split text in SQL Server 2008

1 Answers1