Allow only alphanumeric characters in a column 2


One of my colleague asked me a question that how to “allow only alphanumeric characters” in a column. Below is the exact question:

How can i stop accepting any non-alphanumeric characters during DML operations in a column of a table?

To achieve this, we can follow below steps. There may be more different ways other than these;

  1. Create a table
  2. Create a scalar function which returns true or false by checking non-alphanumeric characters (Using scalar function as an example so that in case of complex logic it can be implemented inside this function.)
  3. Create a check constraint on table and call this created function to check that column has only alphanumeric values

Some other way, we can also create an “Instead Of Trigger” and write a logic to check alphanumeric values for that column which I will not cover in this post.

Now let’s brief me each step mentioned above:

Step 1 – Create a table

Here I am creating a demo table to show you this example.
CREATE TABLE tbl_DemoTable
(
EmpId VARCHAR(50),
Name VARCHAR(100)
)
In above column, we need a requirement to allow only alphanumeric values inside EMPID column.

Step 2 – Create a scalar function which returns true or false by checking non-alphanumeric characters

In this step, we are going to create a scalar function which can check the input value and return true or false as the value is alphanumeric or non-alphanumeric.
CREATE FUNCTION fn_CheckAlphanumeric
(
@EmpId VARCHAR(50)
)
RETURNS BIT
AS
BEGIN
RETURN (SELECT CASE WHEN PATINDEX('%[^a-zA-Z0-9]%', @EmpId) > 0 THEN 0 ELSE 1 END)
END
In this function, i am using PATINDEX function, which can operate with REGEX to check alphanumeric values. Regex to check alphanumeric values is as ‘%[a-zA-Z0-9]%’. And in this regex i have added ^ (Negate operator), so now this regex is as ‘%[^a-zA-Z0-9]%’ which finds the Index position of any non-alphanumeric characters. This regex finds any value which is not in the range of a-z and A-Z.

Step 3 – Create a check constraint on table and call this created function to check that column has only alphanumeric values

Now I am going to bind the created function to this column to achieve our final goal of this post.
ALTER TABLE tbl_DemoTable
ADD CONSTRAINT CK_tbl_DemoTable CHECK (dbo.fn_CheckAlphanumeric(EmpId) = 1)

Testing it with some insert and update operation

Case 1:
INSERT INTO tbl_DemoTable(EmpId, Name)
VALUES('EMP100', 'Test 1')
Output:- 
(1 row(s) affected)
Case 2:
INSERT INTO tbl_DemoTable(EmpId, Name)
VALUES('EMP100-100', 'Test 2')
Output:- 
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint “CK_tbl_DemoTable”. The conflict occurred in database “AdventureWorks2012”, table “dbo.tbl_DemoTable”, column ‘EmpId’.
The statement has been terminated.
Case 3: (Update)
UPDATE tbl_DemoTable SET EMPID = 'EMP100-100' WHERE EMPID = 'EMP100'
Output:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint “CK_tbl_DemoTable”. The conflict occurred in database “AdventureWorks2012”, table “dbo.tbl_DemoTable”, column ‘EmpId’.
The statement has been terminated.

Conclusion

In this article, we have learnt a method of controlling the input data during DML operations. We have also used REGEX and PATINDEX and learnt when we can use these things. Using an “INSTEAD OF TRIGGERS“, we can also achieve this task and there we have no need to create this function and instead of writing this Regex logic inside a function and binding it with the column, we can write the whole logic inside the trigger. But as per recommendations, constraints are  good as per performance point of view in comparison of a trigger (at least in this scenario). If we have very complex logic then trigger can also be a good option or may be the only option. Performance may vary as per the requirement and environment and other factors also.

Here I am not saying that this is the only way to do this. You can also apply directly ‘%[a-zA-Z0-9]%’ this constraint at table creation level or by adding constraints as below;
ALTER TABLE tbl_DemoTable
ADD CONSTRAINT CK_tbl_DemoTable CHECK (1 = CASE WHEN PATINDEX('%[^a-zA-Z0-9]%', EmpId) > 0 THEN 0 ELSE 1 END)

or

ALTER TABLE tbl_DemoTable
ADD CONSTRAINT CK_tbl_DemoTable CHECK (EmpId NOT LIKE'%[^a-zA-Z0-9 ]%')

There are many ways to achieve this.

Some other benefits of implementing constraints instead of trigger is as;

  1.  You will not needed to change your constraint or created function even if you are changing the structure of your table. Constraint has bounded with only EMPID column and will not affect any other. In case of trigger (Instead of Trigger), you have to handle that column inside the trigger and thus needs a change in trigger’s definition.
  2. You have no need to worry about the order of execution of the trigger.
  3. In RDBMS systems, Entity integrity should always be enforced at the lowest level. Thus constraint is better than trigger if we are talking about this also.
There can be some other situation where only trigger can be a way to implement the same. In that case we have no need to think about this comparison. All these things depends on situations.
Thanks for your patients and reading this post. Please do share and rate this article if you really enjoy it and don’t forget to put your valuable comments.
Rate This
[Total: 0    Average: 0/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.


Leave a Reply to Gopal Krishna Ranjan Cancel reply

Your email address will not be published. Required fields are marked *

*

2 thoughts on “Allow only alphanumeric characters in a column

    • Gopal Krishna Ranjan
      Gopal Krishna Ranjan

      Thanks for your appreciation Avinash and keep posting your comments and suggestions. It makes me a feeling of good job.