Home » SQL & PL/SQL » SQL & PL/SQL » get numbers out of a char
get numbers out of a char [message #204894] Wed, 22 November 2006 09:32 Go to next message
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
Re: get numbers out of a char [message #204900 is a reply to message #204894] Wed, 22 November 2006 09:42 Go to previous messageGo to next message
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.
Re: get numbers out of a char [message #204904 is a reply to message #204894] Wed, 22 November 2006 09:50 Go to previous messageGo to next message
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
Re: get numbers out of a char [message #204907 is a reply to message #204904] Wed, 22 November 2006 09:55 Go to previous messageGo to next message
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>;
Re: get numbers out of a char [message #204908 is a reply to message #204904] Wed, 22 November 2006 09:58 Go to previous message
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:
  


Current Time: Wed Dec 07 12:44:36 CST 2016

Total time taken to generate the page: 0.11428 seconds