select only number from varchar2

#1 (**permalink**) 02-22-2008, 06:25 PM

for example I have this records in varchar2 column:

A123D
BLA124XX
125
lkl567836785366AW
2Q

and now I need select only number for spool :

123
124
125
567836785366
2

Have you any idea?

#2 (**permalink**) 02-22-2008, 06:26 PM

<rx@wo.cz> wrote in message news:<av6tdv$clm6a$1@ID-123390.news.dfncis.de>...
> for example I have this records in varchar2 column:
>
> A123D
> BLA124XX
> 125
> lkl567836785366AW
> 2Q
>
> and now I need select only number for spool :
>
> 123
> 124
> 125
> 567836785366
> 2
>
>
> Have you any idea?

Look up usage of TRANSLATE(char, from, to). Here is a quick short example,

SQL> SELECT TRANSLATE('AB123CD', '123ABCD', '123') FROM DUAL;

TRA
---
123

/Rauf Sarwar

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode