Mother of all substrings?

[robert030975]

HI

I have a number of products which have a composition against each however ....


Product Comp
12345 63% POLYESTER 32% VISCOSE 5% ELASTANE
12346 100% VISCOSE
Is there any way to break this down to sepreate

Col1 Col2
63% Polyester
100% VISCOSE
etc

thanks

 

[simian336]

Sure, google "sql server split" and you will get a number of good split functions that you can adapt.

Basically they all use a varation of the

CHARINDEX(@delimiter, @list, 1) function with position markers.

Is this a one time thing or something you will need to do often?

You probably need to include your product number in your new table as well.

Simi

 

[markros]

In your case you just need to split on the space.

Here is how I will do it:

declare @t table (Product int ,Comp varchar(max))
insert into @t
select
12345,   '63% POLYESTER 32% VISCOSE 5% ELASTANE'
union all select
12346,  '100% VISCOSE'

select Product, F.Percents, F0.TheRest, F1.Material from
@t cross apply (select SUBSTRING(Comp,1,charindex(' ',Comp +' ') -1) as Percents) F
cross apply (select SUBSTRING(Comp, len(Percents) +2, Len(Comp)) as TheRest) F0
cross apply (select SUBSTRING(TheRest,1,charindex(' ',TheRest +' ') -1) as Material) F1

PluralSight Learning Library

 

[robert030975]

Hi Markos


Appreciate the code which is great but how would do
say

Percents , Material , Percents2, Materials 2
63 %, Polyester, 32%, Viscose
100% Viscose


Many Thanks


Robert

 

[markros]

That will be a bit more complex. I suggest to split Comp data based on the space and then

;with Split as (select P.Product, F.Pos, F.Value from Products P cross apply dbo.fnSplit(P.Comp,' ') F)

select Product, 
max(case when Pos = 1 then Value end) as Percent1,
max(case when Pos = 2 then Value end) as Material1,

max(case when Pos = 3 then Value end) as Percent2,
max(case when Pos = 4 then Value end) as Material2
from Split 
group by Product

You may want to check this recent blog
Parsing multiple ranges to SP showing the split function you can use also providing links to several good blogs on the split string topic.

PluralSight Learning Library