How to Allow Only Alphabets in Column? – Interview Question of the Week #136

Question: How to Allow Only Alphabets in Column?

Answer: This was a real world scenario and I have mixed feeling about this kind of question being asked in interviews. However, let us accept this question as it is and let us see how to solve this problem.

To allow only alphabets in a column the user can create a check constraint over column to allow insertion of alphabets in the column. Here is a sample script where I demonstrate how users can create constraint over column first so it only allows alphabets.

Let us see the script how we can do so.

USE tempdb
GO
-- Create Test table
CREATE TABLE TestTable
(ID INT, FirstCol VARCHAR(100),
CONSTRAINT FirstCol CHECK (FirstCol NOT LIKE '%[^A-Z]%'))
GO
-- This will be successful
INSERT INTO TestTable (ID, FirstCol)
VALUES (1, 'SQLAuthority')
GO
-- This will throw an error
INSERT INTO TestTable (ID, FirstCol)
VALUES (1, 'SQLAuthority 1')
GO
-- Clean up
DROP TABLE TestTable
GO

Well, that’s it. A good constraint solves the problem about allowing only alphabets in the column. If you have any other solution, please post it over here. I have previously seen many samples, but none of the example really worked well.

So if you have a good solution, I request you to post in the comments area and I will post it with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)