Forum Stats

  • 3,734,034 Users
  • 2,246,862 Discussions
  • 7,857,003 Comments

Discussions

Insert into 2 fields value of one field

423911
423911 Member Posts: 3
edited April 2007 in SQL & PL/SQL
I am trying to separate this value into 2 different fields.

Select last_name from clients1;

will return

' Lopez Gonzalez'

What I am trying to do is put 'Lopez' in last_name_1 and 'Gonzalez' in last_name_2 of a table.

The query will be returning around 3000 last names.

Any help?
Thanks,

Comments

  • 519688
    519688 Member Posts: 2,646
    first name:
    substr( 'Lopez Gonzalez' , 1, instr('Lopez Gonzalez',' ')-1)

    last name:
    substr( 'Lopez Gonzalez' , instr('Lopez Gonzalez',' ')+1)

    so,
    insert into t2 (l, f)
    select substr( n, instr(n,' ')+1), substr( n, 1, instr(n,' ')-1)
    from t1
  • Warren Tolentino
    Warren Tolentino Member Posts: 5,510 Silver Badge
    edited March 2007
    SQL> select t.name full_name,
      2         substr(t.name,1,instr(t.name,' ')-1) first_name,
      3         substr(t.name,instr(t.name,' ')+1) last_name 
      4    from (select 'Lopez Gonzalez' name 
      5            from dual) t;
    
    FULL_NAME      FIRST_NAME     LAST_NAME
    -------------- -------------- --------------
    Lopez Gonzalez Lopez          Gonzalez
    
    SQL> 
    note: that the example from the above gurantees only if there are two literal words in the string.
  • 423911
    423911 Member Posts: 3
    Thanks shoblock!!!!!!!

    Tried it and wala, problem solved..

    Mike.
  • 423911
    423911 Member Posts: 3
    Thanks to both.!!
  • marias
    marias Member Posts: 1,529
    edited March 2007
    another one with REGEXP...

    select REGEXP_SUBSTR('Lopez Gonzalez', '[^ ]+') fname,
    REGEXP_SUBSTR('Lopez Gonzalez', '[^ ]+',regexp_instr('Lopez Gonzalez','[^ ]*$')) lname
    from dual;

    FNAME LNAME
    ----- --------
    Lopez Gonzalez
  • MScallion
    MScallion Member Posts: 1,267
    For fun...
    SQL> select 
       regexp_replace('Lopez Gonzalez', '(.*) (.*)', '\1') first_name, 
       regexp_replace('Lopez Gonzalez', '(.*) (.*)', '\2') last_name
    from dual
    
    FIRST LAST_NAM
    ----- --------
    Lopez Gonzalez
    
    1 row selected.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    col "first" for a20
    col "second" for a20
    col "third" for a20

    select Val,
    Ltrim(REGEXP_SUBSTR(Val,'(^| )[^ ]+',1,1)) as "first",
    Ltrim(REGEXP_SUBSTR(Val,'(^| )[^ ]+',1,2)) as "second",
    Ltrim(REGEXP_SUBSTR(Val,'(^| )[^ ]+',1,3)) as "third"
    from (select 'Oracle Taro' as Val from dual
    union select 'Augustus de Morgan' from dual
    union select 'Wendy Wendy' from dual
    union select 'Lawrence Joseph Ellison' from dual);
This discussion has been closed.