Substrings in Informix SQL
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.