DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: how to enter only a-z or A-Z in a field( no numerals)

  1. #1
    Join Date
    Jul 2006
    Posts
    4

    how to enter only a-z or A-Z in a field( no numerals)

    hello... i have been trying to figure how to enter only a-z or A-Z characters in a name field for an db application. please give the SQL command as an example....

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool


    Try the TRANSLATE() function:
    Code:
     IF translate(COL1,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','a')
        IS NULL     
    THEN
      RAISE_APPLICATION_ERROR(-20001,'Only [a-z][A-Z] Allowed!");
    END IF;
    -- etc --
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Apr 2006
    Posts
    377
    Code:
    SQL> create table test_char (
      2    a1 varchar2(2000)
      3    constraint ck1 check (length(translate(lower(a1),
      4       '#abcdefghijklmnopqrstuvwxyz','#')) = 0));
    
    Table created.
    
    SQL> insert into test_char values ('ABC');
    
    1 row created.
    
    SQL> insert into test_char values ('A1BC');
    insert into test_char values ('A1BC')
    *
    ERROR at line 1:
    ORA-02290: check constraint (SCOTT.CK1) violated
    
    
    SQL> insert into test_char values ('A#BC');
    insert into test_char values ('A#BC')
    *
    ERROR at line 1:
    ORA-02290: check constraint (SCOTT.CK1) violated

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Code:
    create table t1
    ( name varchar2(3)
           check ( ( (ascii(substr(name,1,1)) between 65 and 90  )
                   or (ascii(substr(name,1,1)) between 97 and 122  ) )
           and   ( (ascii(substr(name,2,1)) between 65 and 90  )
                   or (ascii(substr(name,2,1)) between 97 and 122  ) )
           and   (  (ascii(substr(name,3,1)) between 65 and 90  )
                   or (ascii(substr(name,3,1)) between 97 and 122  ) )
           ),
      dob date );
    
    SQL> insert into t1 values ('abc', sysdate) ;
    
    1 row created.
    
    
    SQL> insert into t1 values ('a8a', sysdate) ;
    insert into t1 values ('a8a', sysdate)
    *
    ERROR at line 1:
    ORA-02290: check constraint (TAMIL.SYS_C003114) violated
    
    
    SQL> select * from t1 ;
    
    NAM DOB
    --- ---------
    abc 11-JUL-06
    Tamil

  5. #5
    Join Date
    May 2005
    Location
    France
    Posts
    34
    I personnally like REGEXP_LIKE :
    Code:
    rbaraer@Ora10g> create table testtable
    (
        name VARCHAR2(50 CHAR) CONSTRAINT TESTTABLE_NAME_CHECK CHECK (REGEXP_LIKE(name,'^[a-zA-Z]+$'))
    );  2    3    4
    
    Table created.
    
    rbaraer@Ora10g> INSERT INTO testtable(name) VALUES ('FooBar');
    
    1 row created.
    
    rbaraer@Ora10g> INSERT INTO testtable(name) VALUES ('1FooBar');
    INSERT INTO testtable(name) VALUES ('1FooBar')
    *
    ERROR at line 1:
    ORA-02290: check constraint (RBARAER.TESTTABLE_NAME_CHECK) violated
    
    
    rbaraer@Ora10g> INSERT INTO testtable(name) VALUES ('Foo35Ba7r');
    INSERT INTO testtable(name) VALUES ('Foo35Ba7r')
    *
    ERROR at line 1:
    ORA-02290: check constraint (RBARAER.TESTTABLE_NAME_CHECK) violated
    
    
    rbaraer@Ora10g> INSERT INTO testtable(name) VALUES ('Foobar69');
    INSERT INTO testtable(name) VALUES ('Foobar69')
    *
    ERROR at line 1:
    ORA-02290: check constraint (RBARAER.TESTTABLE_NAME_CHECK) violated
    
    
    rbaraer@Ora10g>
    BTW I prefer using a CHECK constraint compared to a test in PLSQL because the constraint will validate the data even when inserted "by hand", that is not using the PLSQL procedure.

    That does not mean that I wouldn't handle the error in PLSQL procedures, but instead of re-writing the test in PLSQL, I prefer to catch the exception, check the name of the constraint that is violated and send back an appropriated message to the caller.

    To achieve this I create a package header with the exceptions I want to catch, I will use only exceptions defined within this package. I also create the corresponding codes that will be used for RAISE_APPLICATION_ERROR (to be completed depending on your needs ) :

    Code:
    CREATE OR REPLACE PACKAGE PKG_Exception AS
    
        Check_Constraint_Violated EXCEPTION;
        PRAGMA EXCEPTION_INIT(Check_Constraint_Violated, -2290);
        
        Check_Constraint_Violated_Code NUMBER := -20005;
        
    END PKG_Exception;
    /
    Then create a function to get the constraint name from a ORA- message where a constraint has been violated (unique, foreign key, check...). Here I create it stand-alone, but this function had better be in another package :

    Code:
    CREATE OR REPLACE FUNCTION GetConstraintName(
        strErrorMessage IN VARCHAR2,
        strSchemaName IN BOOLEAN DEFAULT TRUE
        ) RETURN VARCHAR2 AS
    
    strTmp VARCHAR2(50 CHAR);
        
    BEGIN
        
        IF (strSchemaName = TRUE) THEN
            RETURN SUBSTR(strErrorMessage, INSTR(strErrorMessage, '(') + 1, INSTR(strErrorMessage, ')') - INSTR(strErrorMessage, '(') - 1);
        ELSE
            strTmp := SUBSTR(strErrorMessage, INSTR(strErrorMessage, '(') + 1, INSTR(strErrorMessage, ')') - INSTR(strErrorMessage, '(') - 1);
            RETURN SUBSTR(strTmp, INSTR(strTmp, '.') + 1);
        END IF;
            
    END GetConstraintName;
    And then use them for handling constraint violations :

    Code:
    BEGIN
    
        INSERT INTO testtable(name) VALUES ('1FooBar');
        
    EXCEPTION
        WHEN PKG_Exception.Check_Constraint_Violated THEN
            CASE GetConstraintName(SQLERRM, FALSE)
            WHEN 'TESTTABLE_NAME_CHECK' THEN
                RAISE_APPLICATION_ERROR(PKG_Exception.Check_Constraint_Violated_Code, 'The name field can only contain alphabetical characters');
            END CASE;
    
    END;
    /
    
    BEGIN
        INSERT INTO testtable(name) VALUES ('1FooBar');
    EXCEPTION
        WHEN PKG_Exception.Check_Constraint_Violated THEN
            CASE GetConstraintName(SQLERRM, TRUE)
            WHEN 'RBARAER.TESTTABLE_NAME_CHECK' THEN
                RAISE_APPLICATION_ERROR(PKG_Exception.Check_Constraint_Violated_Code, 'The name field can only contain alphabetical characters');
            END CASE;
    END;
    /
    In action :

    Code:
    rbaraer@Ora10g> BEGIN
        INSERT INTO testtable(name) VALUES ('1FooBar');
      2    3  EXCEPTION
      4      WHEN PKG_Exception.Check_Constraint_Violated THEN
      5          CASE GetConstraintName(SQLERRM, TRUE)
      6          WHEN 'RBARAER.TESTTABLE_NAME_CHECK' THEN
      7              RAISE_APPLICATION_ERROR(PKG_Exception.Check_Constraint_Violated_Code, 'The name field can only contain alphabetical characters');
      8          END CASE;
      9  END;
     10  /
    BEGIN
    *
    ERROR at line 1:
    ORA-20005: The name field can only contain alphabetical characters
    ORA-06512: at line 7
    
    
    rbaraer@Ora10g>
    HTH & Regards,

    rbaraer

  6. #6
    Join Date
    May 2005
    Location
    France
    Posts
    34
    In fact I think that GetConstraintName should be within PKG_Exception. After all, why another package ? :

    Code:
    CREATE OR REPLACE PACKAGE PKG_Exception AS
    
        Check_Constraint_Violated EXCEPTION;
        PRAGMA EXCEPTION_INIT(Check_Constraint_Violated, -2290);
        
        Check_Constraint_Violated_Code NUMBER := -20005;
        
        FUNCTION GetConstraintName(
        strErrorMessage IN VARCHAR2,
        strSchemaName IN BOOLEAN DEFAULT TRUE
        ) RETURN VARCHAR2;
        
    END PKG_Exception;
    /
    
    CREATE OR REPLACE PACKAGE BODY PKG_Exception AS
        
        FUNCTION GetConstraintName(
            strErrorMessage IN VARCHAR2,
            strSchemaName IN BOOLEAN DEFAULT TRUE
        ) RETURN VARCHAR2 IS
        
        strTmp VARCHAR2(50 CHAR);
        
        BEGIN
        
            IF (strSchemaName = TRUE) THEN
                RETURN SUBSTR(strErrorMessage, INSTR(strErrorMessage, '(') + 1, INSTR(strErrorMessage, ')') - INSTR(strErrorMessage, '(') - 1);
            ELSE
                strTmp := SUBSTR(strErrorMessage, INSTR(strErrorMessage, '(') + 1, INSTR(strErrorMessage, ')') - INSTR(strErrorMessage, '(') - 1);
                RETURN SUBSTR(strTmp, INSTR(strTmp, '.') + 1);
            END IF;
                
        END GetConstraintName;
        
    END PKG_Exception;
    /
    Code:
    rbaraer@Ora10g> BEGIN
    
      2      INSERT INTO testtable(name) VALUES ('1FooBar');
      3    4
      5  EXCEPTION
      6      WHEN PKG_Exception.Check_Constraint_Violated THEN
      7          CASE PKG_Exception.GetConstraintName(SQLERRM, FALSE)
      8          WHEN 'TESTTABLE_NAME_CHECK' THEN
      9              RAISE_APPLICATION_ERROR(PKG_Exception.Check_Constraint_Violated_Code, 'The name field can only contain alphabetical characters');
     10          END CASE;
     11
     12  END;
     13  /
    BEGIN
    *
    ERROR at line 1:
    ORA-20005: The name field can only contain alphabetical characters
    ORA-06512: at line 9
    
    
    rbaraer@Ora10g>
    Regards,

    rbaraer

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Very Impressive reply, Rbaraer.

    Tamil

  8. #8
    Join Date
    May 2005
    Location
    France
    Posts
    34
    Thanks Tamil,

    HTH the OP ,

    rbaraer

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can consider this option also:
    create table testtable (name varchar2(30) check
    ( regexp_like(name, '^[[:alpha:]]+$'))) ;

    For digits only, change "alpha" to "digit".
    Tamil

Posting Permissions

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


Click Here to Expand Forum to Full Width