This discussion is archived
4 Replies Latest reply: Sep 15, 2012 1:44 AM by Hoek RSS

Function to return digits only

962327 Newbie
Currently Being Moderated
Need to create a function in PL/SQL that will take a phone number with formatting such as (07) 6173-8765 and then simply take the numbers out and return 0761738765. The deformat needs to remove all characters and spaces that are not numeric.

I have looked around for some time to find code that will do this but have not had any luck.

Thanks
  • 1. Re: Function to return digits only
    BrendanP Journeyer
    Currently Being Moderated
    WITH d AS (
    SELECT '(07) 6173-8765' X FROM DUAL)
    SELECT x, RegExp_Replace (x, '[^0-9]') 
     FROM d
  • 2. Re: Function to return digits only
    962327 Newbie
    Currently Being Moderated
    Apologies, believe it or not I have worked it out.. and was a lot simpler then I thought. See below if interested:

    CREATE OR REPLACE FUNCTION PHONE_DEFORMAT (
    n_number VARCHAR2)
    RETURN VARCHAR2 IS
    BEGIN
    RETURN REGEXP_REPLACE(n_number,'[^0-9]');
    END;
    /
  • 3. Re: Function to return digits only
    962327 Newbie
    Currently Being Moderated
    Thanks Brendan, actually didn't see your post until I worked it out lol.
  • 4. Re: Function to return digits only
    Hoek Guru
    Currently Being Moderated
    Welcome to the forum.

    Depending on how your PL/SQL function is going to be used (will it also be called from SQL queries?) and the amount of data you need to process, you might also want to test with the 'non-regexp-way':
    SQL> -- generating sample data:
    SQL> with t as (
      2  select '(07) 6173-8765' num from dual
      3  )
      4  --
      5  -- actual query:
      6  --
      7  select replace(translate( num, translate( num, '1234567890', '#'), '#')
      8                , '#'
      9                , null
     10                ) nums
     11  from   t;
    
    NUMS
    ----------
    0761738765
    Regular expressions are very powerful and cool, but also can consume lots of CPU.
    The 'classic' approach is then usually the one that is the fastest.
    So, feel free to test both approaches.

    Don't forget to read the SQL and PL/SQL FAQ by the way: https://forums.oracle.com/forums/ann.jspa?annID=1535
    You can find many interesting articles, like: {message:id=9360010}

Legend

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