Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: constraint creation problems

    I'm trying to add a constraint that will check the firt letter of a VARCHAR type to make sure its first letter is a letter not a number and second to make sure it is in uppercase but I haven't the faintest idea how to do it.

    Does sqlplus support character indexind, in wich case I could probably do something like?:

    AttributeName(1) BETWEEN 'A' AND 'Z'...

    Or do I have to create a domain and check if the forst character of the varchar is in the domain?

    And how do I select only the first character for my checking purposes?

    Any reply will be greatly appreciated!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Try this (In Oracle):

    alter table MyTable
    add constraint chk_UPPER1
    check(nvl(substr(AttributeName,1,1),'?') between 'A' and 'Z');

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2003
    Posts
    4
    Originally posted by LKBrwn_DBA
    Try this (In Oracle):

    alter table MyTable
    add constraint chk_UPPER1
    check(nvl(substr(AttributeName,1,1),'?') between 'A' and 'Z');

    Thanks a bunch!

    Since I'm creating the table can do this?

    CREATE tableName
    (AttributeName varchar(8) not null,
    CONSTRAINT chk_upper1
    check(nvl(substr(AttributeName,1,1),'?') between 'A' and 'Z')
    )

    P.S. I'm not sure if the nvl will work in oracle8 its sqlplus... Than I suppose that I can do it with decode...

    Thanks again!

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation

    Yes, NVL will work in Oracle8 and yes you can define the constraint in the 'create table'

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Oct 2003
    Posts
    4

    Thumbs up

    Originally posted by LKBrwn_DBA
    Yes, NVL will work in Oracle8 and yes you can define the constraint in the 'create table'

    Thanks!

Posting Permissions

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