Check Constraint to allow alphabets only in Sql Server

How to create a check constraint to allow alphabets i.e. A-Z characters only or alphabets + numbers with no special characters in Sql Server?
I think many of us have faced this problem sometimes. Here is the scenario, you have column with varchar/char datatype and you don’t want user to enter any numbers or any Special characters like @, #, $, *. And sometimes characters + numbers with no special characters. And we don’t know any efficient way to handle and we end up writing a long NOT LIKE check constraint.
So let’s see how to write a simple CHECK CONSTARINT for allowing only alphabets and numbers.

First I’ll show you how to write a WHERE conditions for such different cases, so you will get an idea.
Let’s create 1 temporary table and populate it with some data

CREATE TABLE #temp
(ID varchar(50))
GO

INSERT INTO #TEMP SELECT
'Mangal' UNION SELECT
'Mangal Pardeshi' UNION SELECT
'Mangal19' UNION SELECT
'19' UNION SELECT
'@Mangal***' UNION SELECT
'@#$%^&*' UNION SELECT
'Mangal19**'
GO

Now our sample table #temp is ready.
SELECT * FROM #TEMP

-- Output --

@#$%^&*
@Mangal***
19
Mangal
Mangal Pardeshi
Mangal19
Mangal19**
--------------

Case 1 : We just want to get rows with only Alphabets (A-Z characters) with no numbers and Special characters.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z]%'

-- Output --
Mangal
--------------

Now if you notice here ‘Mangal Pardeshi’ din’t get selected as it contains a SPACE in between. To avoid that we need include a single space in our condtion '%[^A-Z]%'. Now we will put '%[^A-Z ]%'.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z ]%'

-- Output --
Mangal
Mangal Pardeshi
--------------

Case 2 : Now we want select all the rows with alphabets + numbers or any of the both with no special characters.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z0-9 ]%'

-- Output --
19
Mangal
Mangal Pardeshi
Mangal19
--------------
Case 3 : Now we want characters + numbers or characters or numbers or a special character *. So I just add a * in our condition.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z0-9* ]%'

-- Output --
19
Mangal
Mangal Pardeshi
Mangal19
Mangal19**
---------------
Same way you can inculde any special character. You just need to add that character into our original condition.

Now writing a Check Constraint is very simple.

For allowing Alphabets only

ALTER TABLE TableName ADD CONSTRAINT Only_Characters CHECK ColumnName NOT LIKE '%[^A-Z ]%'
**Remember to add extra space.

For alphabets + Numbers

ALTER TABLE TableName ADD CONSTRAINT Only_Characters_And_Numebrs CHECK ColumnName NOT LIKE '%[^A-Z0-9 ]%'


Hopefuly this post will help many of you.


- Mangal Pardeshi.