CODEWITHSUNDEEP
sqloracle-database
haoyun
haoyun

Reputation: 57

How to split one column to two columns in oracle sql

Please help me :

First_name | first_name | middle_name
           |            |
rrrr eeee  |   rrrr     | eeee
           |            |    
rrr eeeeeee|   rrr      | eeeeeee  
           |            |       
rr eeeee   |   rr       | eeeee

I want to split the first_name to first_name and middle_name.

Thanks

Upvotes: 2

Views: 10535

Answers (2)

Sebastian Cichosz
Sebastian Cichosz

Reputation: 909

If you really just have to find a space character you can speed things up by removing regular expression

SELECT 
    substr(name, 1, instr(name, ' ')-1) first_name,
    substr(name, instr(name, ' ')+1) middle_name
FROM  TEST

or even

SELECT
  substr(name, 1, space_pos-1) first_name,
  substr(name, space_pos+1) middle_name
FROM (SELECT name,
             instr(name, ' ') space_pos
      FROM TEST)

Upvotes: 2

Gaston Flores
Gaston Flores

Reputation: 2467

Maybe this sql code will be useful:

SELECT 
  REGEXP_SUBSTR(name,'^[^ ]*')  first_name,
  REGEXP_SUBSTR(name, '([[:alpha:]]+)$')   middle_name
FROM TEST

Note: this works if the name always will be FIRSTNAME "SPACE" MIDDLENAME.

You can run this here sql fiddle.

Upvotes: 2

Related Questions