Oracle : SQL using REGEXP_LIKE

Question

I need to extract string from a value of a cell in Oracle table. How can I do that?

VALUE
--------
/var1/var2/var3/C751994ZP1QT11/var4.itp

I want to extract "C751994ZP1QT11" from the value using SQL. I searched some function like REGEXP_LIKE, but I am not quite understand how to use it.

Thanks ahead for the help.

Thanks all for the answer, :) just made a mistake, there is no ** in between. Please see correction above. — Ianthe
– Ianthe, Commented Aug 29, 2012 at 5:33
So what is the criteria? Do you want the 3rd folder name? Do you want the last folder name before the file name? The existing answers show how to extract strings, so you should be able to figure it out. But if you can't, no one can show you how unless you are more specific as to your requirements. — dbenham
– dbenham, Commented Aug 29, 2012 at 12:12

Erkan Haspulat · Accepted Answer · 2012-08-29 05:17:31Z

1

If you want to extract a string, you need to use regexp_substr(). regexp_like() would return a boolean indicating if a certain regular expression matches your string, while regexp_substr() actually returns the matched string.

In your case, assuming you want the string between /** and **/, you could use something like this:

select 
  regexp_substr(
    '/var1/var2/var3/**C751994ZP1QT11**/var4.itp', 
    '/\*\*.*\*\*/') 
from dual

Refer here for some useful shortcuts on regular expressions with Oracle.

answered Aug 29, 2012 at 5:17

Erkan Haspulat

12.7k7 gold badges43 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dbenham · Accepted Answer · 2012-08-29 05:25:51Z

0

select REGEXP_REPLACE('/var1/var2/var3/**C751994ZP1QT11**/var4.itp','.*\*\*(.*)\*\*.*','\1') result from dual

answered Aug 29, 2012 at 5:25

dbenham

132k29 gold badges273 silver badges410 bronze badges

Collectives™ on Stack Overflow

Oracle : SQL using REGEXP_LIKE

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related