need splitting a variable into 6 variables - SQL

Question

need help filling in the blanks i am struggling with splitting a variable into 6 variables.

   DECLARE @item VARCHAR(MAX) = 'MG1111.TG2222.MW3333.JG4444.MG5555.MH6666'
   DECLARE @item1 VARCHAR(MAX) 
   DECLARE @item2 VARCHAR(MAX) 
   DECLARE @item3 VARCHAR(MAX) 
   DECLARE @item4 VARCHAR(MAX) 
   DECLARE @item5 VARCHAR(MAX) 
   DECLARE @item6 VARCHAR(MAX) 

  set @item1 = (SUBSTRING( @item, 0, CHARINDEX('.', @item)))

  set @item2 = (SUBSTRING(SUBSTRING( @item, CHARINDEX('.', @item)+1,LEN(@ITEM)),0,CHARINDEX('.', SUBSTRING( @item, CHARINDEX('.', @item)+1,LEN(@ITEM))) ))

  set @item6 = (REVERSE(SUBSTRING( REVERSE(@ITEM), 0, CHARINDEX('.' , REVERSE(@ITEM)))))

  print @item1
  print @item2
  print @item3
  print @item4
  print @item5
  print @item6

Check this post: http://stackoverflow.com/questions/10914576/t-sql-split-string You will need to `charindex` on `.` rather than `,` in the OP. — msturek, Sep 21 '16 at 16:06
I usually find it better to put this type of thing in a function. Pass to the function how many "." you want to count over to start from (0-5). Have it return the parsed value. Requires you put a loop in the function. More manageable code that way. — M T Head, Sep 21 '16 at 16:11

The One and Only ChemistryBlob · Answer 1 · 2016-09-21T16:18:34.817

0

Why not use this pattern?

set @item1 = SUBSTRING_INDEX( @item, '.', 1)
.
set @item3 = SUBSTRING_INDEX( SUBSTRING_INDEX( @item, '.', 2), '.', 1)
.
.
set @item6 = SUBSTRING_INDEX( @item, '.', -1)

I am assuming by 6 variables you mean each substring between the dots in @item is a separate "variable".

edited Sep 21 '16 at 16:18

answered Sep 21 '16 at 16:12

The One and Only ChemistryBlob

7,864
10
36
69

score 0 · Answer 2 · answered Sep 21 '16 at 16:16

Here is a simpler way using XML trick

DECLARE @item VARCHAR(max) = 'MG1111.TG2222.MW3333.JG4444.MG5555.MH6666' 
DECLARE @item1 VARCHAR(max) 
DECLARE @item2 VARCHAR(max) 
DECLARE @item3 VARCHAR(max) 
DECLARE @item4 VARCHAR(max) 
DECLARE @item5 VARCHAR(max) 
DECLARE @item6 VARCHAR(max); 

WITH split_names 
     AS (SELECT CONVERT(XML, '<Names><name>' + Replace(@item, '.', '</name><name>') + '</name></Names>') AS xmlname) 
SELECT @item1 = xmlname.value('/Names[1]/name[1]', 'varchar(50)'), 
       @item2 = xmlname.value('/Names[1]/name[2]', 'varchar(50)'), 
       @item3 = xmlname.value('/Names[1]/name[3]', 'varchar(50)'), 
       @item4 = xmlname.value('/Names[1]/name[4]', 'varchar(50)'), 
       @item5 = xmlname.value('/Names[1]/name[5]', 'varchar(50)'), 
       @item6 = xmlname.value('/Names[1]/name[6]', 'varchar(50)') 
FROM   split_names

score 0 · Accepted Answer · answered Sep 21 '16 at 16:17

Just about any String Parser will do. That said, I have one that will return (currently) up to 9 variables

Select @item1=Pos1
      ,@item2=Pos2
      ,@item3=Pos3
      ,@item4=Pos4
      ,@item5=Pos5
      ,@item6=Pos6
 From [dbo].[udf-Str-Parse-Row](@Item,'.')

The UDF

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
)

score 0 · Answer 4 · answered Sep 21 '16 at 16:22

A more generic parser could be as follows. This one returns a row number and is not limited as to depth.

DECLARE @item VARCHAR(MAX) = 'MG1111.TG2222.MW3333.JG4444.MG5555.MH6666'
DECLARE @item1 VARCHAR(MAX) 
DECLARE @item2 VARCHAR(MAX) 
DECLARE @item3 VARCHAR(MAX) 
DECLARE @item4 VARCHAR(MAX) 
DECLARE @item5 VARCHAR(MAX) 
DECLARE @item6 VARCHAR(MAX) 

Select @item1=max(case when Key_PS=1 then Key_Value else '' end)
      ,@item2=max(case when Key_PS=2 then Key_Value else '' end)
      ,@item3=max(case when Key_PS=3 then Key_Value else '' end)
      ,@item4=max(case when Key_PS=4 then Key_Value else '' end)
      ,@item5=max(case when Key_PS=5 then Key_Value else '' end)
      ,@item6=max(case when Key_PS=6 then Key_Value else '' end)
  From [dbo].[udf-Str-Parse](@Item,'.')

The 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 ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End

thank you very much, this did the trick :) I don't suppose anyone knows where i can learn in depth about Parse? — Max, Sep 23 '16 at 08:24

need splitting a variable into 6 variables - SQL

4 Answers4