If you have a column in a SQL Server database table that contains character data, but some rows also contain numbers within that data, you can use the following technique to return just those rows that don’t contain numbers.

Numbers can be represented by words and other symbols, but for the purpose of this article, “number” means “numerical digit”. So we’re finding values that don’t contain any numerical digits.

Example

Suppose we have a column with the following data:

SELECT ProductName 
FROM Products;

Result:

+-------------------------------------+
| ProductName                         |
|-------------------------------------|
| Left handed screwdriver             |
| Long Weight (blue)                  |
| Long Weight (green)                 |
| Smash 2000 Sledgehammer             |
| Chainsaw (includes 3 spare fingers) |
| Straw Dog Box                       |
| Bottomless Coffee Mugs (4 Pack)     |
| Right handed screwdriver            |
+-------------------------------------+

This column contains character data, but some rows contain numbers within that character data.

We can use the following query to return just those rows that don’t contain numbers:

SELECT ProductName 
FROM Products
WHERE ProductName NOT LIKE '%[0-9]%';

Result:

+--------------------------+
| ProductName              |
|--------------------------|
| Left handed screwdriver  |
| Long Weight (blue)       |
| Long Weight (green)      |
| Straw Dog Box            |
| Right handed screwdriver |
+--------------------------+

As expected, only those rows that don’t contain numbers are returned.

We can easily flip it around and changer NOT LIKE to LIKE in order to return only those rows that do contain numbers.