CODEWITHSUNDEEP
sqlsql-server
carp47
carp47

Reputation: 47

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

enter image description here

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

Upvotes: 0

Views: 217

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

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

Upvotes: 1

Related Questions