Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Question Unanswered: INSTR equivalent in DB2

    hello,

    is it possible to use a function that made same thing than INSTR (in oracle for example) ?
    I saw that IBM have released a library to do that (in C and in Java), but i need to do it directly in my SQL query ....

    thks

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: INSTR equivalent in DB2

    Are you talking about UDFs (User Defined Functions) ...

    Cheers

    Sathyaram

    Originally posted by lourai
    hello,

    is it possible to use a function that made same thing than INSTR (in oracle for example) ?
    I saw that IBM have released a library to do that (in C and in Java), but i need to do it directly in my SQL query ....

    thks

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    INSTR is oracle's substring function

    in DB2, it's SUBSTR, although it's not as flexible as oracle's

    rudy
    http://rudy.ca/

  4. #4
    Join Date
    Jul 2002
    Posts
    25
    A word of caution here.
    On a UTF8 database you should take care while using SUBSTR on character strings. You might end up getting wrong results if your VARCHAR string has multibyte characters.

    - Vijay

  5. #5
    Join Date
    Jul 2001
    Posts
    32
    Hi,

    INSTR returns position of a character in a string. Substr returns selected string in a string based on the start and end position.

    In DB2, LOCATE(char, string) is equivalent to INSTR(string, char).

    If you want to find out the last occurence of character in a string, in Oracle you can use INSTR(string, char, -1), DB2 we need to write user - defined function.

    I think, This is the correct answer


    Thanks,
    Thangam

  6. #6
    Join Date
    Dec 2002
    Posts
    2
    thanks for all,

    i will test all of that, but the aim was to avoid to write a user-defined function.

    And happy new year to all !

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    THWACK!! (sound of me slapping my forehead)

    of course, oracle's INSTR returns an integer, the position of the substring in the string

    in DB2, that's POSSTR


    rudy

Posting Permissions

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