Sometimes you might need to search a database table for only those rows that contain at least one number in a given column.

Strictly speaking, numbers can be represented by words and other symbols, but for the purpose of this article, “number” means “numerical digit”.

Below is an example of how to use T-SQL to find rows that contain at least one number in SQL Server.

Example

Suppose we have a table called Products with the following data in its ProductName column:

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 obviously contains character data, but some rows contain numbers within that character data (even though they’re not stored as a numeric type).

We can use the following query to return just those rows that contain numbers represented by numerical digits:

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

Result:

+-------------------------------------+
| ProductName                         |
|-------------------------------------|
| Smash 2000 Sledgehammer             |
| Chainsaw (includes 3 spare fingers) |
| Bottomless Coffee Mugs (4 Pack)     |
+-------------------------------------+

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

This technique isn’t limited to just numbers. You could modify this query to include other characters if required. All we’re doing is specifying our own pattern for the LIKE operator.