use of Oracle REGX_SUBSTR to find number between 2 parentheses

Hi there,

I have a table field named Retail_Service_Code.
It has values like FPR (SVC 1008). I need to retrieve the number part from this value. I've never used REGX_SUBSTR before but would it be appropriate to use it here? If not, what would be more appropriate? The number can be 4, 5, or 6 digits long, by the way.

Thanks in advance for your assistance!

Best regards,
wdelaney05Asked:
Who is Participating?
 
sdstuberCommented:
oops, sorry, I didn't take into account the possiblity of non-numeric also being inside the parentheses

11gR2 - regexp_substr(str,'\([^0-9-]*([0-9]+)\)',1,1,null,1)
0
 
sdstuberCommented:
if you have 11gR1 or lower try this...

translate(regexp_substr(str,'\([0-9]+\)'),'-()','-')
0
 
slightwv (䄆 Netminder) Commented:
Just numbers?  Easy:  regexp_substr('FPR (SVC 1008)','[0-9]+')

If the pattern is consistant:

regexp_replace('FPR (SVC 1008)','(.*\([A-Z]{3} )([0-9]+)(\))','\2')
0
 
sdstuberCommented:
if you have 11gR2 you can add subexpressions to the substring extraction

regexp_substr(str,'\(([0-9]+)\)',1,1,null,1)
0
 
sdstuberCommented:
another option, 11gR1 or 10g

this assumes the numeric portion is at the end of the parenthetic substring but ignores any other numerics
 such as 'abc (def1234ghi  5678)'


replace(regexp_substr(str,'[0-9]+\)'),')')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.