Re: Substrings in Informix SQL

Hal Larson wrote: > > I noticed that substrings in Informix SQL only > take a literal as the "from" and "to" fields. > Is there some way to overcome this difficulty? > > Here's what I'm trying to do: I have a CHAR(50) > field of product codes, some of which are appended > with a dash ("-") and then a manufacturer code. > To get the plain vanilla product code, I have to > strip off everything after the dash. The tricky > part is that the length of a product code is > variable, as is the manufacturer code. For > example: > > Product_Code Product_Name Mfr > A349X-99DX Widget Joe's Widgets > A349X-8FDC4 Widget Akhbar's Widgets > A39JJF-9 Whoozit Jerry's Whoozits > > Is there any way I can get a distinct list of products? > > Here's what I have to do now: > > SELECT DISTINCT Product_Code[1,1], Product_Name > FROM Product_List > WHERE Product_Code[2,2]='-' > UNION > SELECT DISTINCT Product_Code[1,2], Product_Name > FROM Product_List > WHERE Product_Code[3,3]='-' > UNION > SELECT DISTINCT Product_Code[1,3], Product_Name > FROM Product_List > WHERE Product_Code[4,4]='-'> ... all the way up to the case where it's a 48-char product code > followed by a dash and a 1-char mfr... > SELECT DISTINCT Product_Code, Product_Name > FROM Product_List > WHERE Product_Code NOT LIKE '%-%';> > Seems a little Kludgy to me, but I don't see any way > around it except to use a stored procedure. I'm > surprised that the subscripts have to be literal > integers, and that there's no substr(str,start,numchars) > function. > > ------------------------------------------------------- > "You're missing the point," said the fork to the spoon. You can do this in version 7.3, which has some string functions. You could write a stored procedure to nibble off characters one at a time from the left end of the string until the dash. The basic idea is to copy character 1 and then remove it from the original string with TRIM. If you can't work it out let me know. Of course, if your database schema had been in first normal form you wouldn't have had this problem (ie the two codes would be in two columns). -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions AND the release notes. All opinions are my own and not those of Bayer plc. My Internet plumbing does not allow me to mail and post news together. Sorry. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/