'SQL - Update 2 columns splitting another column which is separated with undescore [duplicate]

I have 3 columns the 3 are empty and the third contains data as below enter image description here

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.

sqloracle


Solution 1:[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);

Solution 2:[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>

Solution 3:[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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 Littlefoot
Solution 3 Barbaros Özhan

Related Questions