Re: Oracle For Fun

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 29 Jan 2004 01:08:33 -0800
Message-ID: <1075367254.966768@yasure>

Mark Townsend wrote:

> Daniel Morgan wrote:
> 

>> Mark Townsend wrote:
>>
>>> Daniel Morgan wrote:
>>>
>>>> Frank van Bortel wrote:
>>>>
>>>>> <g> COuld you make that a regular? I'd like that.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> My pleasure. I can't remember if I've done this one before ... but
>>>> in the US telephone numbers with area code are always in the form
>>>> ###-###-####.
>>>>
>>>> Write a simple function that will return a Boolean (True/False) as
>>>> to whether something has the format of a valid telephone number with
>>>> area code. It must check for the following:
>>>>
>>>> Three integers between 0 and 9
>>>> Followed by a dash '-'
>>>> Followed by three integers between 0 and 9
>>>> Followed by a dash '-'
>>>> Followed by four integers
>>>> The length must be 12.
>>>>
>>>> Hint: When I run the following query:
>>>> select * from user_source where name = <function_name>;
>>>> it returns only 7 lines of code.
>>>>
>>>
>>> Can I use 10g Regexs ?
>>
>>
>>
>> If you think you can do it better than with 9i features ... post your
>> code: I'm betting you can't. I'll compare what you do with my 9i
>> solution and report back the results.
>>

> Do we have to do the function thing - what if we just return the valid 
> rows ?
> 
> Then the whole thing can be done in a single SELECT. Something like (and 
> I haven't tested this, I don't have 10g at home on the Macs yet, but 
> this is close)
> 
> Select * from table
> where 
> REGEXP_LIKE(number,'^([[:digit:]]{3}-[[:digit:]]{3}-[[:digit:]]{4}|[[:digit:]]{10})$') 
> 
> 

Worse yet Mark ... it isn't even valid:

SQL> CREATE TABLE t (

   2 test VARCHAR2(20));

Table created.

SQL> INSERT INTO t VALUES ('123-45-6789');

1 row created.

SQL> COMMIT; Commit complete.

SQL> SELECT *
   2 FROM t
   3 WHERE
REGEXP_LIKE(test,'^([[:digit:]]{3}-[[:digit:]]{3}-[[:digit:]]{4}|[[:digit:]]{10})$');

no rows selected

Take a look at the "|[[:digit:]]{10}" ;-)

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Thu Jan 29 2004 - 03:08:33 CST