Re: Oracle For Fun

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 29 Jan 2004 00:47:20 -0800
Message-ID: <1075365976.963358@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})$') 

Sorry Mark but we don't have any room in my class until the Summer Intensive starts in June. ;-)

The instructions clearly said a function that returns a Boolean. And you know we fail students for ignoring the instructions.

In your case, however, since you'll likely be here in Seattle on March 20th an allowance is granted. I'll build the function myself.

This will be tested in 10g to see how it performs. Results to be posted in a few days after others take a whack at it. Get me a copy of Beta 12 if you think it will help you. Otherwise it gets tested in Beta 2.

-- 
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 - 02:47:20 CST