[Solved] insert tokenized strings from a table to other [closed]

by


If FULLNAME always consists of 3 parts (which is what you said), then it is relatively simple, using the REGEXP_SUBSTR regular expression function:

SQL> with test (fullname) as
  2    (select 'Metro Goldwyn Mayer' from dual
  3    )
  4  select regexp_substr(fullname, '\w+', 1, 1) first_name,
  5         regexp_substr(fullname, '\w+', 1, 2) middle_name,
  6         regexp_substr(fullname, '\w+', 1, 3) last_name
  7  from test;

FIRST_NAME           MIDDLE_NAME          LAST_NAME
-------------------- -------------------- --------------------
Metro                Goldwyn              Mayer

SQL>

No problem in doing it using classic SUBSTR + INSTR combination, but – that’s not a pretty solution:

SQL> with test (fullname) as
  2    (select 'Metro Goldwyn Mayer' from dual
  3    )
  4  select substr(fullname, 1, instr(fullname, ' ', 1, 1) - 1) first_name,
  5         substr(fullname,
  6                instr(fullname, ' ', 1, 1) + 1,
  7                instr(fullname, ' ', 1, 2) - instr(fullname, ' ', 1, 1) - 1) middle_name,
  8         substr(fullname,
  9                instr(fullname, ' ', 1, 2) + 1) last_name
 10  from test;

FIRST_NAME           MIDDLE_NAME          LAST_NAME
-------------------- -------------------- --------------------
Metro                Goldwyn              Mayer

SQL>

This is the first part of your task; write the INSERT statement yourself. If you can’t make it work, edit your initial message, provide code you managed to work, explain what’s wrong and someone will assist.

solved insert tokenized strings from a table to other [closed]