Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34

    Unanswered: substring with a variable length

    Hi

    I am trying to select portions of data. I tried to use substr but not all the fields are the same size so I either have too much data returned or parts of the data needed are not returned. I'll provide a few examples of what I would like to do.

    Data What I want to select
    ABC-123 ABC
    ABCD-123 ABCD

    Thanks

    Hamza

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    so select only those characters to the left of the dash character ("-").
    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
    substr(column1,1,instr(column1,'-')-1)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34
    Thanks. Here's another scenario suppose I have data with 2 dashes how would I select the set of data I want.

    example what I want to select

    123-ABC-123 ABC
    123-ABCD-123 ABCD

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You are supposed to be doing your own homework assignments!
    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.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    hint: look into instr(). There's a parameter for 'occurrences'.

    INSTR

    --=cf

  7. #7
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34
    Thanks, I figured it out.

    substr(column1,instr(column1,'-',1),instr(column1,'-',2)-instr(column1,'-',1)-1)

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Or, if you like regular expressions (and your database version supports them), it can be done in a simple manner:
    Code:
    SQL> WITH test AS
      2    (SELECT '123-ABC-123' col FROM dual
      3     UNION
      4     SELECT '123-ABCD-123' col FROM dual
      5    )
      6  SELECT col, REGEXP_SUBSTR(col, '[[:alpha:]]+') result
      7  FROM test;
    
    COL          RESULT
    ------------ ------------
    123-ABC-123  ABC
    123-ABCD-123 ABCD
    
    SQL>

Posting Permissions

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