Home » SQL & PL/SQL » SQL & PL/SQL » Extract number digits from varchar and converting it to a number
Extract number digits from varchar and converting it to a number [message #332468] Tue, 08 July 2008 11:07 Go to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi,

I need to convert a varchar string to a number.

For example,

'123' should give me result as 123.
similarly
'123abc' -> 123
'123abc1' -> 123

We cannot acheive the above result by using to_number function as it throws exception.

Can anyone please let me know how to achieve the desired result?

Thanks in advance.

prashas_d.
Re: Extract number digits from varchar and converting it to a number [message #332473 is a reply to message #332468] Tue, 08 July 2008 11:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>'123abc1' -> 123
Why? What is trailing (rightmost) 1 ignored?
Re: Extract number digits from varchar and converting it to a number [message #332479 is a reply to message #332473] Tue, 08 July 2008 11:36 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi Anacedent,

I want to get the numeric digits that are only at the beginning. Once a chacter is occurred starting from left, then it should ignore the remaining string from that position.

Please help me in providing some pointer to achieve the desired result.

Thanks.
Re: Extract number digits from varchar and converting it to a number [message #332481 is a reply to message #332468] Tue, 08 July 2008 11:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select substr('123abc1',1,regexp_instr('123abc1','[^[:digit:]]')-1) from dual

Regards
Michel
Re: Extract number digits from varchar and converting it to a number [message #332482 is a reply to message #332468] Tue, 08 July 2008 11:44 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
If you are in 10g or later, you may use regular expression function, e.g.
SQL> select regexp_substr( '123', '^[0-9]*' ) s1,
  2         regexp_substr( '123abc', '^[0-9]*' ) s2,
  3         regexp_substr( '123abc1', '^[0-9]*' ) s3
  4    from dual;

S1  S2  S3
--- --- ---
123 123 123

SQL> 
Re: Extract number digits from varchar and converting it to a number [message #332486 is a reply to message #332468] Tue, 08 July 2008 11:49 Go to previous message
prashas_d
Messages: 66
Registered: February 2007
Member
Micheal & Flyboy,

Thanks for providing me the ideas.

Its working and I am getting the desired result Smile

prashas_d.
Previous Topic: problem while executing Ref cusror
Next Topic: How to convert the TimeStamp from EST to CET??
Goto Forum:
  


Current Time: Sat Dec 10 13:01:38 CST 2016

Total time taken to generate the page: 0.04184 seconds