SQL SERVER – How to Allow Only Alphabets in Column – Create Check Constraint to Insert Only Alphabets

SQL SERVER - How to Allow Only Alphabets in Column - Create Check Constraint to Insert Only Alphabets alphabets 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)

Previous Post
SQLAuthority News – Notes from SQLLive 360 – Orlando, Nov 2013 – A Great Experience
Next Post
SQL SERVER – Working with Business Days in SQL Server – A Different Approach

Related Posts

No results found.

11 Comments. Leave new

  • Pinal, this solution is deceptively simple, can you explain why “WHERE FirstCol LIKE ‘%[A-Z]%’ ” is not the correct solution

    Reply
    • 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

      Reply
  • 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

    Reply
    • 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.

      Reply
  • 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]%’

    Reply
  • “WHERE FirstCol NOT LIKE ‘%[0-9]%’ ”

    :)

    Reply
  • 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

    Reply
  • if i don’t want none english letter such as : Ònchann, how to protect?

    Reply
  • Tayyaba Farhat
    June 10, 2017 4:36 am

    can you please elaborate how not like is working with \%[^a-z]%’

    Reply
    • Muhammad Abubakar Riaz
      April 17, 2019 12:31 pm

      This is due your “collation” settings that dictates that small and capital abc should be assumed same character.

      Reply
  • webdesignvietnam
    December 10, 2019 7:40 am

    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

    Reply

Leave a Reply

Menu