CODEWITHSUNDEEP
sqlstringoracle-databasesql-updatesubstring
belmer01
belmer01

Reputation: 127

Oracle split string and update in new column

I have 3 columns to capture a person's name: first_name, middle_name, and last_name. I have several entries where the first and middle names are in the first_name column.

first_name   | middle_name | last_name
------------------------------------
James Leroy  |             | Bond

I need to move the middle name into the middle_name column so my table looks like this.

first_name | middle_name | last_name
------------------------------------
James      | Leroy       | Bond

Upvotes: 0

Views: 839

Answers (3)

Shafiqul Islam
Shafiqul Islam

Reputation: 124

In Oracle, if we want to update an existing column splitting by '/' character (for example), with the substring appeared after the '/' in each column. This can be done easily using SUBSTR() method in Oracle as follows:

UPDATE TABLE_NAME
SET COLUMN_NAME=SUBSTR(
        COLUMN_NAME, 
        INSTR(COLUMN_NAME, '/')+1,
        LENGTH(COLUMN_NAME)-1
) WHERE <CONDITIONS HERE>;

Upvotes: 0

GMB
GMB

Reputation: 222432

This task is not complex enough to require a regex. Simple string functions can be used, and should probably be preferred, since they are less expensive:

update mytable
set 
    first_name = substr(first_name, 1, instr(first_name, ' ') - 1),
    last_name  = substr(first_name, instr(first_name, ' ') + 1)
where instr(first_name, ' ') > 0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

What pain. Assuming there is at most one space in the name:

update t
    set first_name = regexp_substr(first_name, '[^ ]+'),
        middle_name = regexp_substr(first_name, '[^ ]+$)
    where first_name like '% %'

Upvotes: 0

Related Questions