Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2003
    Posts
    15

    Unanswered: How to retrieve specific string when end position or string not known

    Hi,

    I need to retrieve cid in the following string

    cjk!cid-timestamp-cid-timestamp. The value for cid is unknown.

    Does anybody know what select statement should I use to archive this?

    Thanks,

    Katya

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How to retrieve specific string when end position or string not known
    Now that you've shared with us (TINU) what you don't know, IMO it would be useful if you told us what you do know.
    You ask the person who decided that jamming different data elements into a single field/column how to extract the desired data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Which cid do you want? The first one or the second and are the delimiters always the same?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jul 2003
    Posts
    15
    Hi,

    I want to retrieve both cids and delimiters are always the same.

    Thanks,

    Katya

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Cid1 := Substr(string,instr(string,'!')+1,instr(string,'-') - Instr(string,'!') - 1);


    Cid2 := Substr(string,instr(string,'-',2)+1,instr(string,'-',3) - Instr(string,'-',2) - 1);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jul 2003
    Posts
    15
    Thank you very much, Bill. I really appreciate it.

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    This works on 10g+
    Code:
    SQL>
    SQL> var x varchar2(150)
    SQL>
    SQL> exec :x := 'cjk!cid-timestamp-cid-timestamp';
    
    PL/SQL procedure successfully completed.
    
    SQL> select regexp_replace( :x, '^([a-z]{1,}\!)(.*)(\-[a-z]{1,}\-)(.*)(\-[a-z]{1,})', '\2 \4' )
      2    from dual
      3  /
    
    REGEXP_REPLACE(:X,'^([A-Z]{1,}\!)(.*)(\-[A-Z]{1,}\-)(.*)(\-[A-Z]{1,})','\2\4')
    --------------------------------------------------------------------------------
    cid cid
    
    SQL>

  8. #8
    Join Date
    May 2006
    Posts
    132
    Another option in addition to JMartinez's solution for 10g+:

    Code:
    SQL> select regexp_replace('cjk!cid-timestamp-cid-timestamp',
      2     '^(.*!)([^-]+)(-[^-]+-)([^-]+)(.*)', '\2 \4') from dual;
    
    REGEXP_
    -------
    cid cid

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You guys make me sick with regular expressions

  10. #10
    Join Date
    May 2006
    Posts
    132
    Quote Originally Posted by Littlefoot
    You guys make me sick with regular expressions
    LOL...makes me sick trying to learn regular expressions sometimes.

Posting Permissions

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