Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: how find character in string

    Hi,
    I've table TAB_TEST:

    ID............DESC_ID
    1.............MY TABLE->YOUR TABLE
    2.............DOG->X
    6.............MY CATS->YOUR CATS
    9.............WINDOWS->Y
    7.............MY JOB->YOUR JOB

    I'd like to write one query with DESC_ID after character "->"

    In my case:

    ID............DESC_ID
    1.............YOUR TABLE
    2.............X
    6.............YOUR CATS
    9.............Y
    7.............YOUR JOB

    How can I find the character "->" in DESC_ID and write from that character in then?


    Thanks in advance!

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    INSTR returns the position in string1 of string2, e.g.

    INSTR('DOG->X','->')

    returns 4.

    SUBSTR returns the substring of string1 starting at position m, n characters long (or until the end of the string if length is not specified); e.g:

    SUBSTR('DOG->X',6)

    returns 'X'. Therefore you just need

    Code:
    SELECT SUBSTR(desc_id,INSTR(desc_id,'->') +2) FROM tab_test;
    There are more options with INSTR and SUBSTR, e.g. to find the nth occurrence instead of just the first one, SUBSTR can work backwards from the end etc. The SUBSTRB, SUBSTRC etc variations are to support multi-byte character sets.
    Last edited by WilliamR; 03-20-06 at 08:30.

Posting Permissions

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