OraFAQ Forum: SQL & PL/SQL » Extracting the Numeric values from a column in oracle

Home » SQL & PL/SQL » SQL & PL/SQL » Extracting the Numeric values from a column in oracle (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Extracting the Numeric values from a column in oracle [message #639450]

Tue, 07 July 2015 08:18

msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad

Member

Hi Experts,

We have a requirement to extract the first occurrence of Numeric digits from a column containing variable combinations:-

For example :-

    x_test
 -------------
1) APC~123~456~BQY
2) INVXR~7890~1
3) 123456~UY
4) IPV~XXYE~1234
5) 12356

so expected output would be
1) 123
2) 7890
3) 123456
4) 1234
5) 12356

Regards

Report message to a moderator

Re: Extracting the Numeric values from a column in oracle [message #639452 is a reply to message #639450]

Tue, 07 July 2015 08:41

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select regexp_substr('APC~123~456~BQY','\d+') res from dual;
RES
---
123

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Report message to a moderator

Re: Extracting the Numeric values from a column in oracle [message #639454 is a reply to message #639452]

Tue, 07 July 2015 08:53

msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad

Member

Thanks Michel!!

Report message to a moderator

Previous Topic:	PL/SQL Cursor
Next Topic:	How to update substring using sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 07 19:03:56 CDT 2026