Is there a function in Informix SQL that will return the position of a substring? I have tried INSTR(), POSITION(), AT()...

The manuals I have for Informix SQL give no indication as to the existence of such a function.

Here's an example of what I'm trying to do:

SELECT c.last_name,
at('-',c.last_name)
FROM client c
WHERE c.last_name LIKE '%-%'

Thanks in advance!!!
Susan

Hi Susan,
Please can you mix between rpad, lpad, replace and lenght function.

Gustavo.

You could use a function such as:

drop function locate;
create function locate(p_instring varchar(255), p_lookfor char(1)) returning integer;

define w_work varchar(255);
define w_token char(1);
define w_location smallint;
define i smallint;

if ((p_instring is NULL) or (p_lookfor = '')) then
let w_location = -1;
return w_location;
end if;

let w_work = trim(p_instring);
let w_token = p_lookfor;
let w_location = 0;

for i = 1 to length(w_work)
if ( substr(w_work,i,1) = w_token ) then
let w_location = i;
exit for ;
end if;
end for;

return w_location;
end function;

-- Disclaimer
--Only finds a single character
--Only find the first occurrence