Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    76

    Unanswered: REGEX - string manipulation

    Hi,

    I have a simple problem that for most of you, would be a piece of cake to solve, certainly.

    I need to retrieve the First Name of a String regarding the following rules:
    - If there is an Space in the String, I must return the SUBSTR until that position.
    Example:
    'Master Aucrun' - must return 'Master'
    - If there is not any Space in the string, I must return all of it.
    Example:
    'MasterAucrun' - must return 'MasterAucrun'
    -Finally, if the String is NULL, then it must return nothing.

    I know how to do it using PL/SQL for it ... but I'l bet that there is an way of solving it with an REGEX expression. Am I right?

    thanks in advance for any help you could spare!

    Master Aucrun

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why not use INSTR() & SUBSRT() function directly?
    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
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you insist on regular expressions solution, here's one option:
    Code:
    SQL> with test as
      2    (select 'Master Aucrun' col from dual union all
      3     select 'MasterAucrun'      from dual union all
      4     select to_char(null)       from dual
      5    )
      6  select
      7    col,
      8    regexp_substr(col, '^\w+') result
      9  from test;
    
    COL           RESULT
    ------------- -------------
    Master Aucrun Master
    MasterAucrun  MasterAucrun
    
    
    SQL>

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    and if you want to use instr and substr which is faster then regular expression the use
    Code:
      1   with test as
      2   (select 'Master Aucrun' col from dual union all
      3    select 'MasterAucrun'      from dual union all
      4    select to_char(null)       from dual
      5    )
      6   select
      7    col,
      8    substr(col,1,instr(col||' ',' ')-1) result
      9*  from test;
    
    COL           RESULT
    ------------- -------------
    Master Aucrun Master
    MasterAucrun  MasterAucrun
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Dec 2003
    Posts
    76
    Hi,

    First of all, thanks for your time and answers.

    As I've written in my first post, I know how to solve it using PL/SQL (SUBSTR and INSTR) , but I would like to know the solution in REGEX form.

    Thanks,
    Master Aucrun

  6. #6
    Join Date
    May 2014
    Location
    World Wide On The Web
    Posts
    16
    Quote Originally Posted by aucrun View Post
    I know how to solve it using PL/SQL (SUBSTR and INSTR)
    It could be done in plain "SQL" using SUBSTR and INSTR, see Bill's solution above.
    Regards,
    Lalit

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by aucrun View Post
    ... but I would like to know the solution in REGEX form
    Didn't you see the 3rd message in this discussion?

  8. #8
    Join Date
    Dec 2003
    Posts
    76
    Quote Originally Posted by Littlefoot View Post
    Didn't you see the 3rd message in this discussion?
    Oooppppsss! I missed it.
    Sorry ...

    I've tried and it works just fine!
    Thanks Littlefoot (and everyone else, of course)

    Best regards,
    Master Aucrun

Tags for this Thread

Posting Permissions

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