Home » SQL & PL/SQL » SQL & PL/SQL » Stripping formatting characters (SQL Tools 1.42, Oracle 10g, xp pro)
Stripping formatting characters [message #331305] Wed, 02 July 2008 23:38 Go to next message
psychoconker
Messages: 8
Registered: July 2008
Junior Member
I am fairly new to pl/sql and sql and I can't figure out how to strip formatting characters from a phone number.. I haven't began to write the code for this part yet because I been trying to think of the best way, whether it be a Substr with a instr or if there was a more easier way.. Basically the phone numbers are going to be formatted in any fashion I.E. (555) 555-5555, 555.555.5555 , (555)555.5555, 555-555-5555 and so on. I was thinking that Instr and Substr would just make it wild and have to have too many lines of code.. Anyone please help? This is going to be a function..
Re: Stripping formatting characters [message #331313 is a reply to message #331305] Wed, 02 July 2008 23:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Try REPLACE or TRANSLATE.
Re: Stripping formatting characters [message #331322 is a reply to message #331305] Thu, 03 July 2008 00:24 Go to previous messageGo to next message
psychoconker
Messages: 8
Registered: July 2008
Junior Member
Using TRANSLATE you can only have 2 arguments correct? So, I couldn't do something like this?

TRANSLATE('(444).444-4444', '(', NULL, ')', NULL, '-', NULL, '.', NULL);

I'd have to use a TRANSLATE for each symbol?
Re: Stripping formatting characters [message #331327 is a reply to message #331322] Thu, 03 July 2008 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
TRANSLATE takes string as parameter:
SQL> select TRANSLATE('(444).444-4444', '0()-.', '0') from dual;
TRANSLATE(
----------
4444444444

1 row selected.

Regards
Michel
Re: Stripping formatting characters [message #331331 is a reply to message #331327] Thu, 03 July 2008 00:49 Go to previous messageGo to next message
psychoconker
Messages: 8
Registered: July 2008
Junior Member
Tried it out, Works perfectly.. Thanks!
Re: Stripping formatting characters [message #331337 is a reply to message #331331] Thu, 03 July 2008 01:20 Go to previous messageGo to next message
psychoconker
Messages: 8
Registered: July 2008
Junior Member
Got it all working but once I threw in some If Statements I'M throwing the error:


PLS-00103: Encountered the symbol "RETURN" when expecting one of the following:

* & - + / at mod remainder rem then <an exponent (**)> and or
|| multiset

CREATE OR REPLACE FUNCTION AREACODE(TEMPNUM IN VARCHAR2) 
RETURN VARCHAR2
AS
  PNUM  VARCHAR2(16);
BEGIN

  PNUM := TRANSLATE(TEMPNUM, '0()-.', '0');

  
  IF Length(PNUM) = 7
    RETURN NULL;
  ELSIF Length(PNUM) = 10
    RETURN  SubStr(PNUM,1,3);
  ELSIF Length(PNUM) = 11 AND SubStr(PNUM,1,1) = '1'
    RETURN  SubStr(PNUM,2,3);
  ELSE
    Raise_Application_Error(-20001, 'Invaid Phone Number.');

EXCEPTION
  WHEN No_Data_Found
    RETURN NULL;
END;
/




Any Suggestions? I'm stumped
Re: Stripping formatting characters [message #331342 is a reply to message #331337] Thu, 03 July 2008 01:27 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
if... THEN
Re: Stripping formatting characters [message #331345 is a reply to message #331342] Thu, 03 July 2008 01:29 Go to previous messageGo to next message
psychoconker
Messages: 8
Registered: July 2008
Junior Member
Wow... I can't believe I looked passed that.. Its getting late.. wow..Thanks
Re: Stripping formatting characters [message #331358 is a reply to message #331337] Thu, 03 July 2008 02:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
An END IF at the end is considered polite too.
Re: Stripping formatting characters [message #331843 is a reply to message #331345] Sat, 05 July 2008 23:16 Go to previous message
prtz
Messages: 11
Registered: January 2008
Junior Member
You could do that processing in plain SQL as well, thereby avoiding the context-switches associated with a function call for each row of your table.

Here's an example:

SQL>
SQL>
--
with t as (
  select '(123)456-7890' as x from dual union all
  select '1-567-289-9098' from dual union all
  select '728 345 8290'   from dual union all
  select '1234-5678'      from dual union all
  select null             from dual union all
  select '498-8767'       from dual union all
  select '123-726-0023'   from dual)
--
select
  x,
  case
    when length(translate(x,'0()-.','0')) = 7  then null
    when length(translate(x,'0()-.','0')) = 10 then substr(translate(x,'0()-.','0'),1,3)
    when length(translate(x,'0()-.','0')) = 11 and substr(translate(x,'0()-.','0'),1,1) = '1'
    then substr(translate(x,'0()-.','0'),2,3)
    else 'Invalid Phone Number'
  end as areacode
from t;

X              AREACODE
-------------- --------------------
(123)456-7890  123
1-567-289-9098 567
728 345 8290   Invalid Phone Number
1234-5678      Invalid Phone Number
               Invalid Phone Number
498-8767
123-726-0023   123

7 rows selected.

SQL>
SQL>


If you are on version 10g or higher, you could harness the power of Regular Expressions as well.

Also, note that the third phone number, in the example above, could (should?) be considered valid.

SQL>
SQL>
SQL> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product

SQL>
SQL>
SQL>
--
with t as (
  select '(123)456-7890' as x from dual union all
  select '1-567-289-9098' from dual union all
  select '728 345 8290'   from dual union all
  select '1234-5678'      from dual union all
  select null             from dual union all
  select '498-8767'       from dual union all
  select '123-726-0023'   from dual)
--
select
  x,
  case
    when length(regexp_replace(x,'\D')) = 7  then null
    when length(regexp_replace(x,'\D')) = 10 then substr(regexp_replace(x,'\D'),1,3)
    when length(regexp_replace(x,'\D')) = 11 and substr(regexp_replace(x,'\D'),1,1) = '1'
    then substr(regexp_replace(x,'\D'),2,3)
    else 'Invalid Phone Number'
  end as areacode
from t;

X              AREACODE
-------------- --------------------
(123)456-7890  123
1-567-289-9098 567
728 345 8290   728
1234-5678      Invalid Phone Number
               Invalid Phone Number
498-8767
123-726-0023   123

7 rows selected.

SQL>
SQL>


HTH
prtz
Previous Topic: creating global temporary table
Next Topic: Problem: Need help!
Goto Forum:
  


Current Time: Thu Feb 13 10:59:17 CST 2025