none
Allow only certain special characters in a column - please help! RRS feed

  • Question

  • Hi,
    I have a table with 3 columns. Say, EmployeeID, EmployeeName and Address.

    From the source all the three columns are coming with some special characters.

    In the EmployeeID column only a-z and 0-9 characters are allowed. 
    In the EmployeeName column only few special characters are allowed  (& _-'), along with a-z and 0-9 . 
    In the Address column only few special characters are allowed   (: , -. )  along with a-z and 0-9.   

    How can i do that?  - If they give what are the special characters are NOT allowed to come, then its simple. But they have given what are allowed to come - I am not sure.
    Please help. Thanks in advance!
    Tuesday, November 12, 2019 10:52 PM

All replies

  • Hi,

    You can create table with constraints allowing only valid values as mentioned above. 

    Wednesday, November 13, 2019 3:15 AM
  • You can create table with constraints allowing only valid values as mentioned above. 

    CHECK constraints to be precise: Allow only alphanumeric characters in a column
    Wednesday, November 13, 2019 3:48 AM
  • You can create table with constraints allowing only valid values as mentioned above. 

    CHECK constraints to be precise: Allow only alphanumeric characters in a column

    create table t4 (c1 varchar(20)  check (patindex('%[^A-Z0-9]%',c1) = 0))

    That will only accept A-Z and 0-9 for Employee id, other conditions can be created also.


    • Edited by Soumen Barua Wednesday, November 13, 2019 4:54 AM
    Wednesday, November 13, 2019 4:49 AM
  • Thanks -  But unfortunately I can't create table or creating constraints. Already there is a stored procedure to pull these three columns and I just need to add some code to implement these logics. Please help!
    Wednesday, November 13, 2019 5:04 AM
  • Thanks -  But unfortunately I can't create table or creating constraints. Already there is a stored procedure to pull these three columns and I just need to add some code to implement these logics. Please help!

    Like this:

    select EmployeeId 
    from mytable
    where patindex('%[^A-Z0-9]%',EmployeeID) = 0

    Mark as answer if it works. Thanks.
    Wednesday, November 13, 2019 5:29 AM