CODEWITHSUNDEEP
sql-serversql-server-2008
user131602
user131602

Reputation: 21

How to create multiple columns from a delimited string

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.

Upvotes: 1

Views: 388

Answers (2)

Narsimha
Narsimha

Reputation: 184

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

Upvotes: 0

nathan_jr
nathan_jr

Reputation: 9282

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.

Upvotes: 1

Related Questions