-
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
/
Cheers!
OraKid.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|