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  |
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 #331322 is a reply to message #331305] |
Thu, 03 July 2008 00:24   |
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 #331337 is a reply to message #331331] |
Thu, 03 July 2008 01:20   |
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 #331843 is a reply to message #331345] |
Sat, 05 July 2008 23:16  |
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
|
|
|
Goto Forum:
Current Time: Thu Feb 13 10:59:17 CST 2025
|