I want remove float number from string
I have tried below code.
select (2.73 lb. ,'[[:alpha:]]|_') from dual;select regexp_replace('2.73 lb.', ' *[[:alpha:]].*$', '') from dual;
<snip> That's not even valid SQL. Please post exact SQL you are using. However, I think you mean:
hm wrote:Well from OPs data it looks that he want to remove suffixes from the data .he/she probably is storing numbers with weight suffixes.
performance is only one thing. The other are the results:
What will be the result for '123.456 abc 456.78' with your code and what will be the result with my regexp_substr?
(I don't know what the OP need's in that case, cause he did'nt thell us)
As you can see, your requirement definition lacks on completeness a little bit.
with data as ( select '2.73 lb.' s from dual union all select 'ada.adsa .73 lb.' s from dual ) select s ,rtrim( translate( s ,'1'||translate( s ,'a1234567890.' ,'a' ) ,'1' ) ,'.' ) r from data S R 2.73 lb. 2.73 ada.adsa .73 lb. ..73