This content has been marked as final. Show 53 replies
well, the easiest way would be just by using substr function if you know exactly where this number starts and how long it is, e.g.
select substr( 'Sample_510_Type' , 8, 3 ) from dual;
Thanks for your kind help! Although it is not an accurate solution. I might not be able to know how long the integer is.
For eg, Temp_3573084048_Std
The number can be this long.
Then please specify what other possiblities are there and what you want to extract.
Examples will help, I'll start with a few, you provide some more and tell us what you want to extract.
Temp_3573084048_Std Temp_0_Std Std_1_Temp Std_153,320,000.01_Temp _7_ Temp82Std T_2_19_2974_best_of 1234
Thanks for your kind help! Although it is not anit is a accurate and performant solution according to what you specified in your first post.
You could use regular expressions...
SQL> ed Wrote file afiedt.buf 1 with t as (select 'fred_12354_asd' as txt from dual union 2 select 'bob_123.43_dfgh' from dual) 3 -- END OF TEST DATA 4 select regexp_replace(txt, '[[:alpha:]]|_') as num 5* from t SQL> / NUM --------------- 123.43 12354 Elapsed: 00:00:00.00 SQL>
Sorry about this.
My case is closest to this example, Temp_3573084048_Std.
I just want to extract out the integer value from that string using Sql command.
Any help is appreciated! Thank you very much!
you could try
Oh Wow! COOL! THANK YOU SO MUCH BLUSHADOW!
It works MIRCULE!! WOW!
Nice one, although you wasted one character. ;-)
with t as (select 'fred_12354_asd' as txt from dual union select 'bob_123.43_dfgh' from dual) -- END OF TEST DATA select regexp_replace(txt, '[[:alpha:]]|_') as num , regexp_replace(txt, '[[:alpha:]_]') as num from t ;
Nice one, although you wasted one character. ;-)Bleeeerggghhh!
Too early in the morning to "think". ;)
What if the database is 9i and i do not know the exact location where my number is going to start in the string data?
In this case regexp_like wont work right? So what options i have for 9i?
So what options i have for 9i?Here is one:
SQL> with t as ( select 'temp_123_rome' str from dual union all select 'for_123455_r' str from dual union all select '1234_sdfk' str from dual union all select 'u0039jjidood' str from dual) select str, translate(str,'x' || translate(str,'x0123456789','x'),'x') from t / STR TRANSLATE(STR ------------- ------------- temp_123_rome 123 for_123455_r 123455 1234_sdfk 1234 u0039jjidood 0039
Thanks michaels it works like magic, but i have one trap for you:
Use x_sdfk_12 in the string, you will get x12 as answer which is wrong :-()
I guess these are some of the exceptions.. anyways thanks for your brain work!
Hi,I don't see that it matter as far as the OP is concerned as he clearly has 10g or above and it worked. It was put forwards as a possible solution, not the one and only solution, so don't be picky unless it's your own question.
What if the database is 9i and i do not know the
exact location where my number is going to start in
the string data?
As michaels has shown it's possible using translate to strip out the non-numeric characters.