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.

SQL SERVER - Retrieving Rows With All Alphabets From Alphanumeric Data alpha1

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'

SQL SERVER - Retrieving Rows With All Alphabets From Alphanumeric Data dataset

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

SQL SERVER - Retrieving Rows With All Alphabets From Alphanumeric Data result1

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

SQL SERVER - Retrieving Rows With All Alphabets From Alphanumeric Data result2

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

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)

Quest

SQL Function, SQL Scripts, SQL Server, SQL String
Previous Post
SQL SERVER – Rename Physical Database File Name for Any Database
Next Post
SQL SERVER – Generating In-Memory OLTP Migration CheckList

Related Posts

4 Comments. Leave new

  • Hi There

    This will also work

    select * from #temp where id not like ‘%[0-9]%’

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

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

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

    Reply

Leave a Reply