8 Replies Latest reply: Feb 18, 2013 11:15 PM by Senthilkumar S RSS

    Remove float number from string

    Senthilkumar S
      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
          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
            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
              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
                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
                  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
                    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
                      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