Here is the question I just received in email.
“Pinal,
I searched your blog at https://blog.sqlauthority.com/search-sqlauthority/ but I could not find a solution to my problem.
I have a column where I only want to allow alphabets. If anyone wants to insert number or any other value in that table, I want the user to see the error from SQL Server.
Would you please help?”
A very interesting question – 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.
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
If you know any other method, please post in the comment. I will post an alternate solution with due credit to this blog.
Reference: Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Pinal, this solution is deceptively simple, can you explain why “WHERE FirstCol LIKE ‘%[A-Z]%’ ” is not the correct solution
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
Hi,
Normally i validate such thing in front end,or before insert in proc.
Suppose I create such constraint then what will be its impact in performance.Like I use that table in select as column or i use that column in where condition.
Thanks
I do agree with you Kumar Harsh. These sort of validations would be better to be handled in front end. But what if someone updates or insert the records directly on the database? In some cases, it is not avoidable to have a constraint like this.
Hi,Andrew G
WHERE FirstCol LIKE ‘%[A-Z]%’ will look for existence of alphabets, but it will not check if there is any non-alphabets
example
select col from
(
select ‘AG12’ as col union all select ‘test’
) as t
WHERE col LIKE ‘%[A-Z]%’
“WHERE FirstCol NOT LIKE ‘%[0-9]%’ ”
:)
Hi,
This accepts only Alphabets in Column
USE tempdb
GO
— Create Test table
CREATE TABLE TestTable
(ID INT, FirstCol VARCHAR(100),
CONSTRAINT FirstCol CHECK (FirstCol LIKE ‘[A-Z]’))
GO
— This will be successful
INSERT INTO TestTable (ID, FirstCol)
VALUES (1, ‘S’)
GO
— This will throw an error
INSERT INTO TestTable (ID, FirstCol)
VALUES (1, ‘SQLAuthority’)
GO
— This will throw an error
INSERT INTO TestTable (ID, FirstCol)
VALUES (1, ‘1’)
GO
— Clean up
DROP TABLE TestTable
GO
if i don’t want none english letter such as : Ònchann, how to protect?
can you please elaborate how not like is working with \%[^a-z]%’
This is due your “collation” settings that dictates that small and capital abc should be assumed same character.
ALTER TABLE table_users
ADD CONSTRAINT username_char_integer
CHECK(username LIKE ‘%[^a-zA-Z0-9]%’);
— CHECK(username LIKE ‘^[^a-zA-Z0-9]*$’);
I am add constraint for table_users[username] but not work. I still insert abc%#$$ .. Could you help, How do I add ?
Regards