3 Replies Latest reply: Feb 1, 2013 1:40 PM by Andy Tael-Oracle RSS

    Modifying string when selecting data

    Andy Tael-Oracle
      Hello,
      I need some help figuring out how to accomplish the following:

      When I query my table interest I get the following:
      select phone_number from interest;
      
      PHONE_NUMBER
      (719) 696-5689
      (512) 373-0745
      (269) 999-9999
      (405) 250-0053
      (678) 563-7155
      (406) 456-7654
      But I'd like it to be:
      select phone_number from interest;
      
      PHONE_NUMBER
      7196965689
      5123730745
      2699999999
      4052500053
      6785637155
      4064567654
      And I am not sure how to accomplish this, any great ideas?

      --Andy                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
        • 1. Re: Modifying string when selecting data
          Frank Kulash
          Hi, Andy,

          If you can easily list all the characters you want removed, then using TRANSLATE is simple and efficient:
          SELECT     TRANSLATE ( phone_number
                      , '0 ()-'
                      , '0'
                      )     AS digits_only
          FROM    interest
          ;
          The 2nd argument to TRANSLATE starts with one of the characters that you want to keep, followed by all the characters you want to remove.
          The 3rd argument is just the 1st character from the 2nd argument ('0' in this example). It doesn't matter if this character actually occurs in the 1st argument or not.

          If you want to keep only the digits, and remove all the other characters, whatever they might be, and you don't know what they might be, then TRANSLATE is still the fastest way, but it's more complicated. REGEXP_REPLACE is much simpler:
          SELECT     REGEXP_REPLACE ( phone_number
                           , '\D'
                           )     AS digits_only
          FROM    interest
          ;
          • 2. Re: Modifying string when selecting data
            sb92075
             1* select translate('(719) 696-5689','1234567890 -()','1234567890') from dual
            SQL> /
            
            TRANSLATE(
            ----------
            7196965689
            • 3. Re: Modifying string when selecting data
              Andy Tael-Oracle
              Works like a charm!

              Thanks!