Question: How to Allow Only Alphabets in Column?
Answer: This was a real-world scenario, and I have mixed feelings 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 them.
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.
The Comprehensive Database Performance Health Check is a highly sought-after service designed to address SQL Server performance issues. Clients consistently choose this comprehensive solution when they encounter performance challenges. As the sole expert behind this service, I am here to collaborate with you in building a tailored action plan for resolution, which typically takes 2 to 4 hours, depending on the complexity of your system.
Reference: Pinal Dave (https://blog.sqlauthority.com), Twitter.
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))