Home » SQL & PL/SQL » SQL & PL/SQL » regular expression to evaluate day of week field (Oracle 10g)
regular expression to evaluate day of week field [message #325923] Mon, 09 June 2008 09:20 Go to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hi everyone,

I am trying to apply a check constraint on a field that will hold days of week in a string as such 1= Monday ... 7= Sunday. I need to find the regular expression format that will ensure:

1] No duplicates in days string. (e.g '12337' = wrong)
2] Only digits from 1 to 7 accepted.
3] The string at any time must be less or equal to 7 characters long.

Im using the REGEXP_LIKE function in 10g. I'm quite sure somebody did that before me (otherwise I'll coin the technique and make big bucks licencing it around).

here's the code as i am testing it :

SELECT 1
FROM dual
WHERE REGEXP_LIKE('123a567', '^{1,7}{1}*');


many thanks for a quick reply,
Didier
Re: regular expression to evaluate day of week field [message #325925 is a reply to message #325923] Mon, 09 June 2008 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
didiera wrote on Mon, 09 June 2008 07:20
Hi everyone,

I am trying to apply a check constraint on a field that will hold days of week in a string as such 1= Monday ... 7= Sunday. I need to find the regular expression format that will ensure:

1] No duplicates in days string. (e.g '12337' = wrong)
2] Only digits from 1 to 7 accepted.
3] The string at any time must be less or equal to 7 characters long.

Im using the REGEXP_LIKE function in 10g. I'm quite sure somebody did that before me (otherwise I'll coin the technique and make big bucks licencing it around).

here's the code as i am testing it :

SELECT 1
FROM dual
WHERE REGEXP_LIKE('123a567', '^{1,7}{1}*');


many thanks for a quick reply,
Didier

In My Opinion, this "design" is so BAD on multiple levels, I won't contribute to digging this hole.

Too bad you do not believe in 3rd Normal Form.
Re: regular expression to evaluate day of week field [message #325926 is a reply to message #325925] Mon, 09 June 2008 09:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do it without REGEXP_LIKE quite easily:
SQL> create table trans_test (col_1 varchar2(10));

Table created.

SQL> insert into trans_Test values ('1234567');

1 row created.

SQL> insert into trans_Test values ('12345678');

1 row created.

SQL> insert into trans_Test values ('1234a67');

1 row created.

SQL> insert into trans_Test values ('123457');

1 row created.

SQL> insert into trans_Test values ('123a67');

1 row created.

SQL> 
SQL> SELECT col_1
  2  FROM   trans_test
  3  WHERE  rtrim(translate(substr(col_1,1,7),'1234567',' ')) is not null
  4  OR     length(col_1) >7;

COL_1
----------
12345678
1234a67
123a67
Re: regular expression to evaluate day of week field [message #325932 is a reply to message #325926] Mon, 09 June 2008 10:56 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Dear anacedent,

just to make things clear... the code I provided is only an example, a test that I am working on and definitely not the final product (as it will be defined in the check constraint on my table). Here i'm only evaluating how good regular expression with REGEXP_LIKE works when it comes to validating the conditions I have exposed above. ALL I require so far is that you guys give me the reg-exp combination to achieve what I'm trying to do.
I would appreciate also that you spare me your snobbing next time. My interest in posting questions here is solely related to finding guidance and help, not insults whatsoever (I have my daily share at work already, thanks)!

regards,
Didier
Re: regular expression to evaluate day of week field [message #325934 is a reply to message #325923] Mon, 09 June 2008 10:59 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
JRowBottom,

I'm afraid we didn't understand each other; but it's ok. My previous post should cast a bit more light on what I'm trying to achieve. Just read-on again and I'll be glad to read your input.

thanks,
Didier
Re: regular expression to evaluate day of week field [message #325947 is a reply to message #325932] Mon, 09 June 2008 11:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Apart from whether you like the way he put it or not, Anacedent does have a point. You give an example in which you clearly store data in a single column that should be stored in a detail-table.
The fact that later on you say that does not resemble what you actually want is not his fault or problem: you should have posted your actual problem in the first place then.
Re: regular expression to evaluate day of week field [message #325955 is a reply to message #325923] Mon, 09 June 2008 12:21 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
Try this.
SELECT 1
FROM dual
WHERE REGEXP_LIKE('123a567', '^[1]?[2]?[3]?[4]?[5]?[6]?[7]?$');

It fails for no value - I'm not sure if you needed it to pass since you say:
Quote:
3] The string at any time must be less or equal to 7 characters long.


Re: regular expression to evaluate day of week field [message #325956 is a reply to message #325955] Mon, 09 June 2008 12:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This only allows for numbers in ascending order.
Re: regular expression to evaluate day of week field [message #325957 is a reply to message #325923] Mon, 09 June 2008 13:07 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> ALL I require so far is that you guys give me the reg-exp combination to achieve what I'm trying to do.
REQUIRE?

Nobody owes you any response.

With free advice, you get what you paid for it.

If you don't like any response, you are entitled to a full & complete refund.

Submit all complaints to /dev/null!

Placing multiple values in a single field not how knowledgeable professionals design & implement quality applications.
Re: regular expression to evaluate day of week field [message #326007 is a reply to message #325934] Mon, 09 June 2008 22:37 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Still don't know if this is what you are looking for?

SQL> select * from trans_test;

COL_1
----------
1234567
12345678
1234a67
123457
123a67
123367

6 rows selected.

SQL> select * from trans_test
  2  where regexp_like(col_1, '^[1-7]{1,7}$')
  3  and not regexp_like(col_1, '(.).*\1');

COL_1
----------
1234567
123457
Re: regular expression to evaluate day of week field [message #326009 is a reply to message #325923] Mon, 09 June 2008 22:46 Go to previous message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
thanks a lot ebran and drewsmith70! : Smile

The solutions you proposed suit me well. As you guys see, there was really nothing to it. Thans anyway to you all

regards,
Didier
Previous Topic: Problem on a simple Where Clause !!!
Next Topic: password encryption
Goto Forum:
  


Current Time: Wed Dec 07 12:42:55 CST 2016

Total time taken to generate the page: 0.10765 seconds