CODEWITHSUNDEEP
oracle-database
user3844587
user3844587

Reputation: 19

Extracting first_name and last_name from full_name in Oracle

  1. If input is comma separated then store the first part in LAST_NAME and second part in FIRST_NAME.
  2. If comma is not present then store the name in LAST_NAME

Can you please help me in achieving the #2 in oracle? In my approach am unable to store the full_name in last_name column if there is no comma.

Input :
1. FULL_NAME = "MIKE,MYERS"
2. FULL_NAME = "KFC"

Output :

 SELECT SUBSTR('FULL_NAME', 0, INSTR('1,2', ',') - 1) LAST_NAME,  
        SUBSTR('FULL_NAME', INSTR('1,2', ',', -1) + 1) FIRST_NAME   FROM DUAL

  **LAST_NAME**    **FIRST_NAME**
    MIKE             MYERS

  **LAST_NAME**    **FIRST_NAME**
                     KFC

Upvotes: 0

Views: 3232

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

Since you need two separate columns, I think you would need two CASE statements.

For example,

SQL> WITH DATA AS
  2    ( SELECT 'MIKE,MYERS' str FROM dual
  3    UNION ALL
  4    SELECT 'KFC' str FROM dual
  5    )
  6  SELECT
  7    CASE
  8      WHEN instr(str, ',') <> 0
  9      THEN SUBSTR(str, 1, INSTR(str, ',', 1) - 1)
 10      ELSE str
 11    END LAST_NAME,
 12    CASE
 13      WHEN instr(str, ',') <> 0
 14      THEN SUBSTR(str, INSTR(str, ',', 1) +1)
 15      ELSE NULL
 16    END FIRST_NAME
 17  FROM DATA
 18  /

LAST_NAME  FIRST_NAME
---------- ----------
MIKE       MYERS
KFC

SQL>

Upvotes: 2

Related Questions