I got an email from one of my blog readers asking for an easy way to identify if a given string contains only alphabets. There can be several methods to do this. One simple method is to make use of a regular expression. Let us see how we can retrieve rows with the all alphabets from Alphanumeric Data.
Let us create the following data set to demonstrate this
CREATE TABLE #TEMP (ID VARCHAR(10)) INSERT INTO #TEMP(ID) SELECT '190033' UNION ALL SELECT 'ALMT93' UNION ALL SELECT '192,456' UNION ALL SELECT 'MLZPQIR' UNION ALL SELECT '10AXZZZ' UNION ALL SELECT '067772' UNION ALL SELECT 'XKMPQW'
As you see the column named id has various types of values like alphabets, numbers, and alphanumerics. From this data set, we have to list only the rows that have only alphabets.
Now identify the rows that have not only alphabets but any other characters as well
SELECT ID FROM #TEMP WHERE ID LIKE '%[^a-zA-Z]%'
The result is as shown in the following image
What we need now is just the opposite of it. ie identify the rows that have only alphabets and not any other characters. You can simply use NOT operator before the regular expression
SELECT ID FROM #TEMP WHERE ID NOT LIKE '%[^a-zA-Z]%'
The result is as shown in the following image
In mathematics, Double minus into minus is plus. Similarly NOT and ^ (NOT in the regular expression) resulted in what we need.
You may be interested in the following posts as well
- SQL SERVER – UDF – User Defined Function to Extract Only Numbers From String
- SQL SERVER – UDF – Validate Integer Function
If you know any other methods, please let me know and I will be happy to post the same on the blog with due credit to you.
Reference:Â Pinal Dave (https://blog.SQLAuthority.com)
4 Comments. Leave new
Hi There
This will also work
select * from #temp where id not like ‘%[0-9]%’
Hi Julian,
Your query will return special characters in the result set, if the column contains special characters too (Alphabets + Special Characters combination) . The goal of the query is to retrieve only Alphabets from the column, which in your case will not return the anticipated results.
Thanks,
Srini
Hi
If you also wanted to include in the result set the records that have characters and numbers you can do the following
select * from #temp where isnumeric(id )= 0
result :
ALMT93
MLZPQIR
10AXZZZ
XKMPQW
Hi Pinal,
Why would we need 2 A-Z in the regular expression as mentioned in the query?
SELECT ID FROM #TEMP
WHERE ID NOT LIKE ‘%[^A-ZA-Z]%’
I think only 1 A-Z with negation would Suffice as shown below.
SELECT ID FROM #TEMP
WHERE ID NOT LIKE ‘%[^A-Z]%’
as ^A-Z will check if any of the characters in the column contains non Alphabetic characters (it might be number or any character which is not Alphabet). As we are applying the NOT (negation) before the LIKE, we would ignore these in the result set and retrieve only the Alphabets.
Please correct me if my understanding is wrong.
Thanks,
Srini