Split one string in a cell with variable substring lengths into several different columns using SQL

Question

Below is the data I am using. I am using Jaspersoft Studio version 6.3 and SQL Server 2012. My question is how do you split a cell with a string of variable length into several sub strings with variable length using SQL code?

Example:

This is the string

'MEM - CP NEW INSTALL - 000 - 519.83 - MEMPHIS - SHELBY - TN'

this is the output I would like to get:

MEM
CP NEW INSTALL
000
519.83
MEMPHIS
SHELBY
TN

each in a separate column.

Each of these has a variable length and I am trying to make a generalized code for imported data

You need to do this with a string splitter. Here are some excellent options here. http://sqlperformance.com/2012/07/t-sql-queries/split-strings Then if at all possible you should stop storing multiple values in a singe cell like this, it violates 1NF. — Sean Lange, Aug 09 '16 at 15:21
Possible duplicate of [How to split a single column values to multiple column values?](http://stackoverflow.com/questions/5123585/how-to-split-a-single-column-values-to-multiple-column-values) & [How to split a comma-separated value to columns](http://stackoverflow.com/q/10581772/876298) — Alex K, Aug 09 '16 at 15:27
I have tried those Alex and got the first part to work but the other portions won't show. so that code doesn't work for my case — carp47, Aug 09 '16 at 15:32

John Cappelletti · Accepted Answer · 2016-08-09T15:48:54.030

Perhaps something like this.

Declare @String varchar(max) = 'MEM - CP NEW INSTALL - 000 - 519.83 - MEMPHIS - SHELBY - TN'
Select * from [dbo].[udf-Str-Parse-Row](@String,' - ')

Returns

Pos1    Pos2            Pos3    Pos4    Pos5    Pos6    Pos7    Pos8    Pos9
MEM     CP NEW INSTALL  000     519.83  MEMPHIS SHELBY  TN      NULL    NULL

The UDF - Easy enough to tailor to your needs/size

CREATE FUNCTION [dbo].[udf-Str-Parse-Row] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse-Row]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse-Row]('John Cappelletti',' ')
--       Select * from [dbo].[udf-Str-Parse-Row]('id26,id46|id658,id967','|')

Returns Table 

As

Return (
    SELECT Pos1 = xDim.value('/x[1]','varchar(250)')
          ,Pos2 = xDim.value('/x[2]','varchar(250)')
          ,Pos3 = xDim.value('/x[3]','varchar(250)')
          ,Pos4 = xDim.value('/x[4]','varchar(250)')
          ,Pos5 = xDim.value('/x[5]','varchar(250)')
          ,Pos6 = xDim.value('/x[6]','varchar(250)')
          ,Pos7 = xDim.value('/x[7]','varchar(250)')
          ,Pos8 = xDim.value('/x[8]','varchar(250)')
          ,Pos9 = xDim.value('/x[9]','varchar(250)')
    FROM (Select Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML) as xDim) A
)

Another Option is the standard parsing (with row nr) and then you can pivot as needed

Select * from [dbo].[udf-Str-Parse](@String,' - ')

Returns

Key_PS  Key_Value
1       MEM
2       CP NEW INSTALL
3       000
4       519.83
5       MEMPHIS
6       SHELBY
7       TN

The Second UDF

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
As
Begin
   Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
   Insert Into @ReturnTable Select Key_Value = ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End

Split one string in a cell with variable substring lengths into several different columns using SQL

1 Answers1