I have a string that is delimited: 'abc-defghi-klo'

The delimiter could be any character like '-' in this case, and the text can have any number of characters between the '-'.

How can I extract the last phrase 'klo' from the string using SQL? Basically I would need to find the last '-' in the string and extract from that position onwards.

It would be easy if the Position() function had a starting position parameter that would accept -1 to indicate start searching from the end of the string, but it doesn't.

I don't see any way of doing this with SQL. Is writing a function the only way to do it?

Any suggestions?
TIA

Barry

The best way is probably to start at the end of the string and move backwards until you reach the delimiter:

(this is untested!! ... a function accepting aString VARCHAR and aDelimiter VARCHAR(1))

 DECLARE I INTEGER;
 DECLARE ResultStr VARCHAR;
 DECLARE NewChar VARCHAR(1);

SET I = LENGTH(aString);
WHILE I > -1 DO
 SET NewChar = SUBSTRING(aString FROM I FOR 1);
 IF NewChar = aDelimiter THEN
   SET I = -1;
   ELSE
     SET ResultStr = NewChar + ResultStr;
     SET I = I -1;
   END IF;
 END WHILE;
RETURN ResultStr;

Adam,

Thanks for the code. It got me thinking that all I need is a PosExx() function that does backward string searching with optional offset and case-insensitivity thrown in.

So I wrote one this morning and uploaded to the Extensions forum.

I figure other people might find it useful.
Just look for PosExx().

Enjoy!

Barry

Barry

Thanks. Useful!

I have written a Parse() function that parses values from a delimited string. You can find it in the Extensions forum.

Barry