1

I have a an inventory field with 4 components, each separated by a "-". I'm not sure how to use charindex to parse the string so that I am returning a column for each value found between delmiters. For example: field value or string = item-color-size-shape.

My goal is to end up with a item as column, color as column, size as a column and finally shape.

bobs
  • 21,844
  • 12
  • 67
  • 78
user131602
  • 21
  • 1
  • 1
    possible duplicate of [Split values over multiple rows](http://stackoverflow.com/questions/13159526/split-values-over-multiple-rows) – Taryn Nov 19 '12 at 20:07
  • 2
    Not really a duplicate. Seems we need a column per delimiter, not rows. – nathan_jr Nov 19 '12 at 20:30

2 Answers2

1

If it is max 4 columns you can try like this:

declare @Table table (SomeColumn varchar(100));
insert into @Table
    select 'ball-blue-small-round'  union all
    select 'ball-red-small-round'   union all
    select 'ball-green-small-round' union all
    select 'ball---square'          union all
    select '----';

;with stage (s)
as  (   select  replace(SomeColumn, '-', '.')
        from    @Table
    )
select  [item] = parsename(s,4),
        [color] = parsename(s,3),
        [size] = parsename(s,2),
        [shape] = parsename(s,1)
from    stage;

If its > 4 please reply and we can work on a more dynamic solution.

nathan_jr
  • 9,092
  • 3
  • 40
  • 55
  • I apologize for lateness of reply but was working on another section. I don't understand what you mean with the select and union. I'm new at some of this stuff. – user131602 Nov 21 '12 at 15:03
  • I understand now, you are creating a test table. It just dawned on me..I am trying solution now. – user131602 Nov 21 '12 at 15:17
  • The data columns are skewed to the right with the first column returning a null value – user131602 Nov 21 '12 at 22:05
0

Using CROSS APPLYs (used lengthy names for understanding) -

declare @Table table (SomeColumn varchar(100));
insert into @Table
select 'ball-Orange-small-round'  union all
select 'bat-blue-medium-square'   union all
select 'stumps-green-large-rectangle'

SELECT * FROM @Table

SELECT Sub1.FirstSub1 AS Item
       ,Sub2.SecondSub1 AS Color
       ,Sub3.ThirdSub1 AS Size
       ,SubAfterThirdHyphen AS Shape 

FROM @Table 
CROSS APPLY (SELECT CHARINDEX('-',SomeColumn) AS FirstHyphenPos) AS Pos1
CROSS APPLY (SELECT SUBSTRING(SomeColumn,1,FirstHyphenPos-1) AS FirstSub1) AS Sub1
CROSS APPLY (SELECT SUBSTRING(SomeColumn,FirstHyphenPos+1,LEN(SomeColumn)) AS SubAfterFirstHyphen) AS Substr1

CROSS APPLY (SELECT CHARINDEX('-',Substr1.SubAfterFirstHyphen) AS SecondHyphenPos) AS Pos2
CROSS APPLY (SELECT SUBSTRING(Substr1.SubAfterFirstHyphen,1,SecondHyphenPos-1) AS SecondSub1) AS Sub2
CROSS APPLY (SELECT SUBSTRING(Substr1.SubAfterFirstHyphen,SecondHyphenPos+1,LEN(Substr1.SubAfterFirstHyphen)) AS SubAfterSecondHyphen) AS Substr2

CROSS APPLY (SELECT CHARINDEX('-',Substr2.SubAfterSecondHyphen) AS ThirdHyphenPos) AS Pos3
CROSS APPLY (SELECT SUBSTRING(Substr2.SubAfterSecondHyphen,1,ThirdHyphenPos-1) AS ThirdSub1) AS Sub3
CROSS APPLY (SELECT SUBSTRING(Substr2.SubAfterSecondHyphen,ThirdHyphenPos+1,LEN(Substr2.SubAfterSecondHyphen)) AS SubAfterThirdHyphen) AS Substr3
Narsimha
  • 184
  • 4