Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    6

    Question Unanswered: Extracting first names

    Hi,there is a situation where some forename contain a second name as well as the first. Eg John Fredrick. I just want to extract the John name. I used some functions which work if there is more than one name but if just one name return a Null. The problem is Im looking for a blank character and where there is just one name it returns zero in the INSTR funtion. Because I subtract 1 it it gives a negative in the Substr function.

    The function is:
    substr(forename,1,( instr(forename,' ')-1))

    Any ideas how I can strip out just the first name?
    Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Piece of cake.
    Code:
    SQL> select * from test;
    
    FORENAME
    ------------------------------
    Little
    Big Foot
    John E. Fredrick
    
    SQL> select regexp_substr(forename, '\w+') first_name from test;
    
    FIRST_NAME
    ------------------------------
    Little
    Big
    John
    
    SQL>

  3. #3
    Join Date
    Feb 2012
    Posts
    6
    Thanks for that.

Posting Permissions

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