Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Unanswered: Informix SQL; Finding the Position of a Character

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi Susan,
    Please can you mix between rpad, lpad, replace and lenght function.

    Gustavo.

  3. #3
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    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
    Fred Prose

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •