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)
7 Comments. Leave new
Hi pinal, could you please explain, what is the use of ^ carat in the constraint?
yogi – see https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017
Nice post
Why can’t we write the code like below
— Create Test table
CREATE TABLE TestTable
(ID INT, FirstCol VARCHAR(100),
CONSTRAINT FirstCol CHECK (FirstCol LIKE ‘%[A-Z]%’))
My guess is because using your constraint check “123A123” would be allowed. In your example you are looking for 1 occurrence of a letter, where in the solution answer, the code looks for any character that isn’t a letter and then negates that.
This can we done through
firstcol char(20)
Why can’t we write the code like that
CREATE TABLE TestTable
(ID INT, FirstCol CHAR(100))