Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select statement show partial field SQL 2005 1

Status
Not open for further replies.

jtarry

IS-IT--Management
Mar 10, 2010
22
GB
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

 
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

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

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

Code:
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
 
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
Code:
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
 
Thanks markros,

This is exactly what I require
 
Thanks nicearms for your help as well
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top