SQL SERVER – Retrieving Rows With All Alphabets From Alphanumeric Data

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)