Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Question Unanswered: Equivalent to Oracle's INSTR in DB2 UDB?

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can find the DB2 UDF Equivalents of various Oracle/Sybase functions at
    http://www-106.ibm.com/developerwork...dfs/index.html

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2002
    Posts
    7

    Question Try a UDF

    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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I wonder if this will work for you:
    POSSTR ( source-string , search-string )
    The schema is SYSIBM.
    The POSSTR function returns the starting position of the first occurrence of
    one string (called the search-string) within another string (called the
    source-string). Numbers for the search-string position start at 1 (not 0).

  5. #5
    Join Date
    Jun 2004
    Posts
    9
    Great, exactly what I needed!!

    Thanks!

Posting Permissions

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