SQL - Update 2 columns splitting another column which is separated with undescore

Question

I have 3 columns the 3 are empty and the third contains data as below

I want to update name_id with the numbers before the underscore and the product_id column with the number after the underscore. I am using sql developer. I tried several solution but i didn't find how can i separate them based on the underscore.

Answer 1

Using the base string functions we can try:

UPDATE yourTable
SET name_id = SUBSTR(nameid_p, 1, INSTR(nameid_p, '_') - 1),
    product_id = SUBSTR(nameid_p, INSTR(nameid_p, '_') + 1);

Answer 2

One option might be using regular expressions.

Before:

SQL> select * from test;

NAMEID_P   NAME_ID    PRODUCT_ID
---------- ---------- ----------
123_456
653_857
5633_85

Update; the first one takes digits anchored to the beginning of nameid_p, while the second one anchors to the end of the string:

SQL> update test set
  2    name_id = regexp_substr(nameid_p, '^\d+'),
  3    product_id = regexp_substr(nameid_p, '\d+$');

3 rows updated.

Result:

SQL> select * from test;

NAMEID_P   NAME_ID    PRODUCT_ID
---------- ---------- ----------
123_456    123        456
653_857    653        857
5633_85    5633       85

SQL>

Answer 3

You can basically [^_] pattern in order to split those string values along with REGEXP_SUBSTR() function as option such as

UPDATE t --> your table
   SET name_id = REGEXP_SUBSTR(nameid_p,'[^_]+'),
       product_id = REGEXP_SUBSTR(nameid_p,'[^_]+$')

Demo

No need to apply TO_NUMBER() conversion to the splitted parts during the Update operation, since already Oracle implicitly does this.