Word split in oracle-CodePudding

i have a table

Create table emp_details(eno number,ename varchar2(22));

having values below

eno	ename
1	suresh kumar
2	ram kumar

now i want to split the ename as two columns by using the space.

eno	ename	col1	col2
1	suresh kumar	suresh	kumar
2	ram kumar	ram	kumar

CodePudding user response：

We can use INSTR here for an option using base string options:

SELECT
    eno,
    CASE WHEN INSTR(ename, ' ') > 0
         THEN SUBSTR(ename, 1, INSTR(ename, ' ') - 1)
         ELSE ename END AS col1,
    CASE WHEN INSTR(ename, ' ') > 0
         THEN SUBSTR(ename, INSTR(ename, ' ')   1) END AS col2
FROM yourTable;

Demo

The CASE expressions used above are there to ensure that a one word name gets assigned to the first name only.

CodePudding user response：

You can use regular expressions if the data set is not relatively huge

WITH emp_details(eno,ename) AS
(
 SELECT 1, 'suresh kumar'    FROM dual UNION ALL
 SELECT 2, 'ram kumar'       FROM dual
)
SELECT eno, 
       REGEXP_SUBSTR(ename,'[^ ] ') AS name,
       REGEXP_SUBSTR(ename,'[^ ] $') AS surname
  FROM emp_details

ENO	NAME	SURNAME
1	suresh	kumar
2	ram	kumar

But if there can be people with names more than one word in the data set, then rather prefer using

WITH emp_details(eno,ename) AS
(
 SELECT 1, 'suresh kumar'    FROM dual UNION ALL
 SELECT 2, 'ram kumar'       FROM dual UNION ALL
 SELECT 3, 'ram sahil kumar' FROM dual 
)
SELECT eno, 
       REGEXP_REPLACE(ename, '^(.*) .*$', '\1') AS name,
       REGEXP_SUBSTR(ename,'[^ ] $') AS surname
  FROM emp_details

ENO	NAME	SURNAME
1	suresh	kumar
2	ram	kumar
3	ram sahil	kumar

Demo

CodePudding user response：

If names have two "words", then you might consider regular expressions.

SQL> select eno, regexp_substr(ename, '\w ', 1, 1) first_name,
  2              regexp_substr(ename, '\w ', 1, 2) last_name
  3  from emp_details;

       ENO FIRST_NAME LAST_NAME
---------- ---------- ----------
         1 suresh     kumar
         2 ram        kumar

SQL>