How to create new column based on values after "/" in other column in Oracle?-CodePudding

I have table with "col1" in Oracle:

col1
------
email1.com/GGR2
pokmail.com/AA
rrajd.com.nl/RRe2

I need to create "col2" with values after "/". So as a result I need something like below:

col1              | col2
--------------------------
email1.com/GGR2   |GGR2
pokmail.com/AA    |AA
rrajd.com.nl/RRe2 |RRe2

How can I do that in Oracle?

CodePudding user response：

Regular expressions or substr instr:

SQL> with test (col1) as
  2    (select 'email1.com/GGR2' from dual union all
  3     select 'pokmail.com/AA'  from dual union all
  4     select 'rrajd.com.nl/RRe2' from dual
  5    )
  6  select col1,
  7         regexp_substr(col1, '\w $') col2_first,
  8         --
  9         substr(col1, instr(col1, '/')   1) col2_second
 10  from test;

COL1              COL2_FIRST        COL2_SECOND
----------------- ----------------- -----------------
email1.com/GGR2   GGR2              GGR2
pokmail.com/AA    AA                AA
rrajd.com.nl/RRe2 RRe2              RRe2

SQL>

CodePudding user response：

You can use:

SELECT col1,
       CASE INSTR(col1, '/')
       WHEN 0
       THEN NULL
       ELSE SUBSTR(col1, INSTR(col1, '/')   1)
       END AS col2
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (col1) AS
SELECT 'email1.com/GGR2'   FROM DUAL UNION ALL
SELECT 'pokmail.com/AA'    FROM DUAL UNION ALL
SELECT 'rrajd.com.nl/RRe2' FROM DUAL UNION ALL
SELECT 'example.com'       FROM DUAL;

Outputs:

COL1 COL2

email1.com/GGR2 GGR2

pokmail.com/AA AA

rrajd.com.nl/RRe2 RRe2

example.com

COL1	COL2
email1.com/GGR2	GGR2
pokmail.com/AA	AA
rrajd.com.nl/RRe2	RRe2
example.com

Note: this will work even if the column does not contain a / character. If you just use SUBSTR(col1, INSTR(col1, '/') 1) and the string does not contain / then it will return everything rather than returning NULL.

db<>fiddle here