Hi,

In Oracle 8i I can parse a string for the position of a specific character.

Position = INSTR(String_To_Search, Character being searched, Start search at position x in String_To_Search, Occurence)

So

Position = INSTR('abcabc', 'b', 1, 1)

will return 2 since the first occurence of 'b' when starting the search at position 1 of the string to search is at position 2.

Is there an equivalent in DB2 UDB. Currently running version 7.2, soon to upgrade to 8.1.

Thanks for your help,
Bruno

You can find the DB2 UDF Equivalents of various Oracle/Sybase functions at
http://www-106.ibm.com/developerwork...dfs/index.html

HTH

Sathyaram

There did not seem to be anything in the porting guides.

How about a UDF like below (note this a quick and dirty that you may need to refine). You could also extend it to include start and stop positions.

the query values (admin.instr('test','t')) returns 2

regards

Tony


create function instr(search varchar(200) , string1 varchar(25) ) returns int
contains SQL
begin atomic
declare counter int default 0;
declare position int default 1;
declare found int default 0;
while (0 < locate (string1, search, position)) do
set counter = counter +1;
set position = locate (string1, search, position) + 1;
end while;
return counter;
end

I wonder if this will work for you:

Great, exactly what I needed!!

Thanks!