Re: SQL question: check constraints on strings

Uday Shankar <uday@rfp.rnb.com> asked: >I have a question that I can't seem to find an answer to in the >IIUG FAQ or the Informix documentation. > >I have a column (char strings) in which I want a check such that if a >string I attempt to insert into >that column starts with the substring STK, then I want the string to be >of the format: > >STK.---.--- > >Else, it doesn't have to follow any particular format. > >Is there a way to achieve this ? I looked at the check constraints but >that doesn't seem to help. Well, you don't indicate what the dashes or dots represent, so I'm going to assume that dots represent dots, and dashes represent a digit. I wouldn't expect this to be in the FAQ; after all, it is simply a question of expressing a particular condition in SQL. How would you select all the valid strings from the table? SELECT CharCol FROM WhatEver WHERE (CharCol MATCHES "STK.[0-9][0-][0-9].[0-9][0-9][0-9]" OR CharCol NOT MATCHES "STK*") Therefore, you should be able to write the check constraint with something like: CREATE TABLE WhatEver ( [...] CharCol CHAR(11) NOT NULL CHECK(CharCol MATCHES "STK.[0-9][0-][0-9].[0-9][0-9][0-9]" OR CharCol NOT MATCHES "STK*"), [...] ); Yours, Jonathan Leffler (jleffler@informix.com) #include <disclaimer.h> PS: Warning I do not reply to messages with anti-spam in the return path.