Re: Oracle For Fun

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 29 Jan 2004 07:55:03 -0800
Message-ID: <1075391644.657203@yasure>

Daniel Morgan wrote:

> 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}" ;-)

Boy do I feel foolish. D... Macallan. Well it was, after all 1:00am when I wrote this.

Jump on Mark about not reading the instructions and forgot that I had changed the example from a social security number(###-##-####) to a phone number (###-###-####). Sorry Mark. Mea Culpa. Your solution works as written.

Here's Marks solution in the form of a function that returns a Boolean. It doesn't truly solve the problem in that it accepts 605-555-5555 and 6055555555 which does violate the original instructions. Benchmarks soon.

CREATE OR REPLACE FUNCTION RegEx (string_in VARCHAR2) RETURN BOOLEAN IS

BEGIN
    IF NOT
REGEXP_LIKE(string_in,'^([[:digit:]]{3}-[[:digit:]]{3}-[[:digit:]]{4}|[[:digit:]]{10})$') THEN
      RETURN False;
    END IF;
    RETURN True;
END RegEx;
/

SET SERVEROUTPUT ON BEGIN
   IF RegEx('605-555-5555') THEN
     dbms_output.put_line('True');
   ELSE
     dbms_output.put_line('False');
   END IF;
END;
/

-- 
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 - 09:55:03 CST