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

by

[ad_1]

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.

[ad_2]

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