Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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>
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading