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?
>
You can upgrade to 7.30, where the function SUBSTRING (or was it
SUBSTR?) is built-in.
> 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.
Looks more than a little kludgy to me. What's wrong with a stored
procedure? In any case, even with substr(str, start, numchars), you'd
have to find the location of the '-' in your string.
Here's a little something I whipped up. Just call me "bored".
CREATE PROCEDURE Cut (string VARCHAR(255), delimiter CHAR(1))
RETURNING VARCHAR(255); DEFINE i INTEGER;
DEFINE loc INTEGER;
DEFINE res VARCHAR(255);
LET loc = FindStr(string, delimiter);
IF loc = 0 THEN
RETURN string;
END IF;
LET res = '';
FOR i = 1 TO loc - 1
LET res = res || string[1,1];
LET string = string[2,255];
END FOR;
RETURN res;
END PROCEDURE;
CREATE PROCEDURE FindStr(str VARCHAR(255), ch CHAR(1))
RETURNING INTEGER; DEFINE i INTEGER;
FOR i = 1 TO length(str)
IF str[1,1] = ch THEN
RETURN i;
END IF;
LET str = str[2,255];
END FOR;
RETURN 0;
END PROCEDURE;
Now you should just be able to do a
SELECT DISTINCT Cut(product_code, '-', product_name
FROM product_list
I haven't tested this very thoroughly, but it should at least get you
started.
June
--
june_t@hotmail.com
Lost in the wilds of Palo Alto, living on sushi