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;
- Create a table
- 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.)
- 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
CREATE TABLE tbl_DemoTable ( EmpId VARCHAR(50), Name VARCHAR(100) )
Step 2 – Create a scalar function which returns true or false by checking non-alphanumeric characters
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
Step 3 – Create a check constraint on table and call this created function to check that column has only alphanumeric values
ALTER TABLE tbl_DemoTable ADD CONSTRAINT CK_tbl_DemoTable CHECK (dbo.fn_CheckAlphanumeric(EmpId) = 1)
Testing it with some insert and update operation
INSERT INTO tbl_DemoTable(EmpId, Name)
VALUES('EMP100', 'Test 1')
INSERT INTO tbl_DemoTable(EmpId, Name)
VALUES('EMP100-100', 'Test 2')
UPDATE tbl_DemoTable SET EMPID = 'EMP100-100' WHERE EMPID = 'EMP100'
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.
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;
- 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.
- You have no need to worry about the order of execution of the trigger.
- 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.
Good one…. I really like your articles because they are simple and informative….Thanks for all the works man….
Thanks for your appreciation Avinash and keep posting your comments and suggestions. It makes me a feeling of good job.