This discussion is archived
8 Replies Latest reply: Feb 18, 2013 9:15 PM by SenthilkumarS RSS

Remove float number from string

SenthilkumarS Newbie
Currently Being Moderated
HI

I want remove float number from string

I have tried below code.
select (2.73 lb. ,'[[:alpha:]]|_') from dual;
but for above query, i got o/p :
   2.73   .
but I need to get number and decimal point not other points

Sample o/p
   2.73  
Edited by: 925896 on Feb 15, 2013 2:29 AM
  • 1. Re: Remove float number from string
    Rahul_India Journeyer
    Currently Being Moderated
    For this particular scenario

    select substr('2.73 lb.',1,4) from dual


    or
    select trim('.' from  
                translate
                ('2.73 lb.','1234567890abcdefghijklmnopqrstuvwxyz','1234567890')
               ) 
     
    from dual
    Edited by: Rahul India on Feb 15, 2013 4:42 PM
  • 2. Re: Remove float number from string
    Paul Horth Expert
    Currently Being Moderated
    925896 wrote:
    HI

    I want remove float number from string

    I have tried below code.
    select (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:
    select regexp_replace('2.73 lb.', ' *[[:alpha:]].*$', '') from dual;
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 3. Re: Remove float number from string
    hm Expert
    Currently Being Moderated
    Are you looking for something like this?
    select regexp_substr('2.73 lb.', '\d*(\.?\d*)?') from dual;
  • 4. Re: Remove float number from string
    Rahul_India Journeyer
    Currently Being Moderated
    Hi all
    i want to ask if regular expression will work faster or the query that i have written with translate and trim.

    Regards
    Rahul Raj
  • 5. Re: Remove float number from string
    hm Expert
    Currently Being Moderated
    hi Rahul,

    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 not tell us)

    Edited by: hm on 15.02.2013 04:37
  • 6. Re: Remove float number from string
    Rahul_India Journeyer
    Currently Being Moderated
    hm wrote:
    hi Rahul,

    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)
    Well from OPs data it looks that he want to remove suffixes from the data .he/she probably is storing numbers with weight suffixes.
    So i tailored the query for that scenario only
  • 7. Re: Remove float number from string
    chris227 Guru
    Currently Being Moderated
    It could easily done with translate
    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
    As you can see, your requirement definition lacks on completeness a little bit.
    If it is necessary to handle the second case, we coold easily find a simple solution for this.
    There maybe other unsolved cases, like several "numbers" in the string, which one should be choosen.
    So before starting to find a proper soultion, the requirements must be defined exactly.

    Edited by: chris227 on 15.02.2013 03:30
  • 8. Re: Remove float number from string
    SenthilkumarS Newbie
    Currently Being Moderated
    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points