Home » SQL & PL/SQL » SQL & PL/SQL » Check Constraint (SQL)
Check Constraint [message #570726] Wed, 14 November 2012 20:00 Go to next message
Barro
Messages: 1
Registered: November 2012
Junior Member
I have two columns in my table Start_Semseter and End_Semester

F- Fall
M- Summer
S - Spring and below are the values.

Start_Semester End_Semester
F09 F12
F11 F13
F12 F14
S07 F07
M09 S11

Here I want a add a check constraint such that the start_semester < End_Semester. the ascii values of
F is 70, M is 77 and S is 83. how can I enforce a constraint by comparing ascii values?

Re: Check Constraint [message #570727 is a reply to message #570726] Wed, 14 November 2012 20:01 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Check Constraint [message #570729 is a reply to message #570727] Thu, 15 November 2012 00:13 Go to previous messageGo to next message
Littlefoot
Messages: 19610
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
According to what you said, the fourth row (S07 - F07) would violate that constraint. Is that correct?

Here's one option (straightforward): split strings to characters, find their ASCII values, concatenate them and - finally - compare.
alter table test add constraint ch_se check
  (to_number(ascii(substr(start_semester, 1, 1)) || ascii(substr(start_semester, 2, 1)) || ascii(substr(start_semester, 3, 1))) <
   to_number(ascii(substr(end_semester  , 1, 1)) || ascii(substr(end_semester  , 2, 1)) || ascii(substr(end_semester  , 3, 1)))
  );
Re: Check Constraint [message #570732 is a reply to message #570726] Thu, 15 November 2012 01:08 Go to previous message
Michel Cadot
Messages: 59126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Previous Topic: change External Directory from server to local machine path
Next Topic: Identify table row
Goto Forum:
  


Current Time: Thu Sep 18 09:01:02 CDT 2014

Total time taken to generate the page: 0.07239 seconds