Use Regular Expression to allow specific characters
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Use Regular Expression to allow specific characters

  1. #1
    Join Date
    Dec 2003
    Location
    Mehamadabad
    Posts
    14

    Use Regular Expression to allow specific characters

    Hi,
    I want to put a validation on a column of a table. The column can have only following characters as a valid character.

    0-9, a-z, A-Z, $, *, +, ?, @, , !, %, &, ', -, /, :, (, ), ., ,, #
    if any other characters are used an error is to be returned.

    I tried to use regular expression to solve the problem but i am new to it and not able to find a solution.

    Please help me out.
    Ramchandra Jetwani

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Check Constraint looks like the appropriate tool for this job.

    Code:
    alter YOURTABLE
    add CONSTRAINT check_YOURCOLUMN
       CHECK (YOURCOLUMN IN ('0', '1', '2', ... '#'));
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2006
    Posts
    377
    The following is a regex version:

    Code:
    SQL> create table check_reg (
      2     a1 varchar2(200)
      3     constraint a1_chk check
      4     (regexp_replace(a1, '[''0-9a-zA-Z$*+?@!%&/:().,#-]') is null));
    
    Table created.
    
    SQL> insert into check_reg values ('_');
    insert into check_reg values ('_')
    *
    ERROR at line 1:
    ORA-02290: check constraint (TEST.A1_CHK) violated
    
    
    SQL> insert into check_reg values ('$');
    
    1 row created.
    
    SQL> insert into check_reg values ('ABCD456:)');
    
    1 row created.
    
    SQL> insert into check_reg values ('''');
    
    1 row created.
    
    SQL>
    SQL> select * from check_reg;
    
    A1
    ---------------------------
    $
    ABCD456:)
    '

  4. #4
    Join Date
    Dec 2003
    Location
    Mehamadabad
    Posts
    14
    Thank you very much. The suggestion solved my problem.
    Ramchandra Jetwani

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