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

Here is the question I just received in email.

“Pinal,

I searched your blog at http://search.sqlauthority.com 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 (http://blog.sqlauthority.com)

About these ads

9 thoughts on “SQL SERVER – How to Allow Only Alphabets in Column – Create Check Constraint to Insert Only Alphabets

  1. 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

  2. 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.

  3. 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]%’

  4. Pingback: Dew Drop – December 9, 2013 (#1679) | Morning Dew

  5. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s