1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Filtering out of initials and titles

Discussion in 'SQL PL/SQL' started by Estelle, Jan 25, 2011.

  1. Estelle

    Estelle Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I have a column called name, in which there are multiple formats for names, eg Mr L Struwig & Dr FL Mulder. I need to trim the titles and the initials, and the desired result should be Struwig & Mulder, bearing in mind that an initial can be LE and the surname ROUX, in which case I want ROUX and not LE ROUX. How?
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Some what Like this.

    Code (SQL):

    SQL> SELECT FULL_NAME,
           REGEXP_REPLACE(
                  REGEXP_REPLACE (FULL_NAME,'(Mr|Dr) ','')
                         ,'([[:upper:]]{1,2}) ','') NAME
    FROM TEST_NAME  2    3    4    5  ;

    FULL_NAME                      NAME
    ------------------------------ ------------------------------
    Rajuvan K I                    Rajuvan I
    Mr L Struwig                   Struwig
    Dr FL Mulder                   Mulder
    Mr L Struwig & Dr FL Mulder    Struwig & Mulder
    Mr J Estelle                   Estelle
    Sadik                          Sadik
    Mr B Obama                     Obama

    7 ROWS selected.

    SQL> SELECT FULL_NAME,
           REGEXP_REPLACE (
                 REGEXP_REPLACE(
      2    3    4                      REGEXP_REPLACE (FULL_NAME,
      5                       '(Mr|Dr) ','')
      6               ,'([[:upper:]]{1,2}) ','')
      7          ,'([[:upper:]]{1,2})$','')
      8                        NAME
      9  FROM TEST_NAME
     10  ;

    FULL_NAME                      NAME
    ------------------------------ ------------------------------
    Rajuvan K I                    Rajuvan
    Mr L Struwig                   Struwig
    Dr FL Mulder                   Mulder
    Mr L Struwig & Dr FL Mulder    Struwig & Mulder
    Mr J Estelle                   Estelle
    Sadik                          Sadik
    Mr B Obama                     Obama

    7 ROWS selected.

    SQL>
     
     
  3. Estelle

    Estelle Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thanks Raj - Wow!
     
  4. bsrinu27

    bsrinu27 Active Member

    Messages:
    3
    Likes Received:
    1
    Trophy Points:
    85
    Location:
    hyderabad
    consider the string DR L Y SRINIVAS

    select SUBSTR('DR L Y SRINIVAS',instr('DR L Y SRINIVAS',' ',-1)) from dual