Thanks.

-----Original Message-----
From: rodries2 [mailto:rodri_es@...]
Sent: 17 February 2005 11:48 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: POSITION



--- In firebird-support@yahoogroups.com, "Dion Oliphant"
<doliphant@s...> wrote:

> Hi,
>
> Is it possible to find the occurrence of a char in a string using

DSQL.

> Ie
> SELECT SUBSTRING(aWord FROM 1 TO (need to insert a char position

here))

> from aTable, or will I need to use an SP. Basically I need to split

a

> string(CSV) into its substrings.
>
> Many Thanks,
> Dion.

Hi Dion

see http://www.volny.cz/iprenosil/interbase/ip_ib_code_string.htm

CREATE PROCEDURE Pos (SubStr VARCHAR(100), Str VARCHAR(100))
RETURNS (Pos INTEGER) AS
DECLARE VARIABLE SubStr2 VARCHAR(201); /* 1 + SubStr-lenght + Str-
length */
DECLARE VARIABLE Tmp VARCHAR(100);
BEGIN
IF (SubStr IS NULL OR Str IS NULL)
THEN BEGIN Pos = NULL; EXIT; END

SubStr2 = SubStr || '%';
Tmp = '';
Pos = 1;
WHILE (Str NOT LIKE SubStr2 AND Str NOT LIKE Tmp) DO BEGIN
SubStr2 = '_' || SubStr2;
Tmp = Tmp || '_';
Pos = Pos + 1;
END

IF (Str LIKE Tmp) THEN Pos = 0;
END

Example of calling from another stored procedure:
EXECUTE PROCEDURE Pos 'bc', 'abcdefghij' RETURNING_VALUES :cad_pos;

Regards








Yahoo! Groups Links