Convert a varchar to a number

Hi all,

I've some problem i'd like with a query to have a numeric part
of a field that contains alpha-numeric

e.a
----

Streetnumber : 25/b ---> 25
115b5 --> 115
140/ --> 140



Thx in advance

What is the logic of the alpha-numeric fields. Is it so that first you have the numbers and then the letters?

yes , i know that but i'd like to know if a function exist to retrieve only the numeric part of the data ?


Thx in advance

Here you are:

1 select translate('Streetnumber : 25/b',
2 '01234567890QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm!"#¤%&/()=+?,.;:* -',
3* '01234567890') from dual

TR
--
25


1 select translate('115b5',
2 '01234567890QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm!"#¤%&/()=+?,.;:* -',
3* '01234567890') from dual

TRAN
----
1155


1 select translate('140',
2 '01234567890QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm!"#¤%&/()=+?,.;:* -',
3* '01234567890') from dual

TRA
---
140

If you need to eliminat emore symbols, you can add symbols to the long string.

Your query is good only for number who has only alpha in the end e.a:

25/b --> 25 good
115b5 --> 1155 not good but 115

in fact i just want the number before the 1st character non numeric

thx for you reply

try this:

select substr(translate('140-34',
'01234567890QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm!"#¤%&/()=+?,.;:* -',
'01234567890qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq'),1,
instr(translate('140-',
'01234567890QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm!"#¤%&/()=+?,.;:* -',
'01234567890qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq'),'q')-1) from dual
/