Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unhappy Unanswered: SQL Check Constraint question

    Hi,
    basically I want to know how I can make a constraint to check the input characters are limited to standard characters only (without $%^& ect...).

    This is for the creation of a table but is a relation constraint.

    CREATE TABLE Testtable(
    column_a varchar(2) CONSTRAINT CHECK ("not allowed "$%&^")
    )


    thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL Check Constraint question

    One way:

    CONSTRAINT CHECK (TRANSLATE(column_a,'$%&^','XXXXX') = column_a)

    i.e. specify ALL the invalid characters and translate each one to (say) 'X'. If after translation the string is same as before, it didn't contain any invalid characters.

    Alternatively, if it is easier to specify what IS valid then:

    CONSTRAINT CHECK (TRANSLATE(column_a,'#ABCDE...','#') IS NULL)

    i.e. specify ALL the valid characters and translate each one to NULL (the '#' is just there so that the second string isn't empty). If the translated string is not null, it must have contained an invalid character.

  3. #3
    Join Date
    Nov 2003
    Posts
    2

    Re: SQL Check Constraint question

    Is there a specific key word that can be used to do this or do we have to code it like that?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL Check Constraint question

    No fancy keywords, no.

    The only way to avoid the hardcoded list would be to use a trigger. Then you could loop through each character like this:

    for i in 1..length(:new.column_a) loop
    if substr(:new.column_a,i,1) not between 'A' and 'Z' then
    raise_application_error(...);
    end if;
    end loop;

    But the check constraint will be more efficient.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •