OraFAQ Forum: SQL & PL/SQL » get numbers out of a char

Home » SQL & PL/SQL » SQL & PL/SQL » get numbers out of a char

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

get numbers out of a char [message #204894]

Wed, 22 November 2006 09:32

steffeli
Messages: 112
Registered: July 2006

Senior Member

Hello,

How can I get only the numbers out of a char? Example:

Param1 -> Result (new parameter)
LX4587 -> 4587
U74AAA -> 74
78U8 -> 788

Thanks
Stefan

Report message to a moderator

Re: get numbers out of a char [message #204900 is a reply to message #204894]

Wed, 22 November 2006 09:42

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Here's the paltry low tech solution.

select replace(translate(upper('AS657A3'),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','                          ')
              ,' ',null) 
from dual

Lets see who gets the Regex solution in first.

Report message to a moderator

Re: get numbers out of a char [message #204904 is a reply to message #204894]

Wed, 22 November 2006 09:50

steffeli
Messages: 112
Registered: July 2006

Senior Member

ok, but how can I convert the new parameter into a number format (it comes as a char with your code)?

Thanks Stefan

Report message to a moderator

Re: get numbers out of a char [message #204907 is a reply to message #204904]

Wed, 22 November 2006 09:55

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Regex version converting to number:

select cast(regexp_replace(<column_name>,'[^[:digit:]]') as number) from <table_name>;

Report message to a moderator

Re: get numbers out of a char [message #204908 is a reply to message #204904]

Wed, 22 November 2006 09:58

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Would you like me to come over there and code it for you?

select to_number(replace(translate(upper('AS657A3'),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','                          ')
              ,' ',null))
from dual

Actually - you didn't ask that originally. You asked how to get the numbers out of a string.

[Updated on: Wed, 22 November 2006 09:59]

Report message to a moderator

Previous Topic:	calling a procedure within unix shell script
Next Topic:	procedures and functions tutorial

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 07:38:55 CDT 2024