Select statement show partial field SQL 2005 1

[jtarry]

i need to seperate out the number and description from a field (not created by me!)Table in MSSQL 2005

example of existing Table

Ref id Desc
1234 1 ABCD,ABCD,ABCD
1234 2 CDFD,CDFD

i want new table

REF id qty Desc
1234 1 3 ABCD
1235 2 2 CFFD

Thanks for amy help

 

[NiceArms]

Not 100% sure what you mean but to help with the data in the Desc field:

If all the data in the Desc field is "4 letters","4 letters" do

select ref, id, left(Desc,4)
from table

However if you have differing lengths (and the delimiter is always comma) use:

declare @tmp table 
(ref int, id int, Descript varchar(20))
insert into @tmp values (1234,1,'abcd,abcde,ab')
insert into @tmp values (1235,2,'a3d,abc3e,ab1')
insert into @tmp values (1234,3,'abd,a,ab')

SELECT ref, id, left(descript,charindex(',',descript,1)-1)
FROM @tmp

result is:

1234 1 abcd
1235 2 a3d
1234 3 abd

 

[markros]

Looks like you want to get information before the first comma and count of number of commas + 1 in the Qty field?

If this is correct, then

select Ref, id, Desc, 
substring(Desc,1, charindex(',', Desc + ',') - 1) as CodeVal, len(Desc) - Len(replace(Desc,',','')) + 1 as Qty from myTable

PluralSight Learning Library

 

[jtarry]

Thanks markros,

This is exactly what I require

 

[NiceArms]

Well, I was halfway there ;p

 

[jtarry]

Thanks nicearms for your help as well