If you need to return all rows that contain at least one numerical digit in MySQL, the following example might come in handy.

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             |
| Right handed screwdriver            |
| Long Weight (blue)                  |
| Long Weight (green)                 |
| Smash 2000 Sledge Hammer            |
| Chainsaw (Includes 5 spare fingers) |
| Straw Dog Box                       |
| Bottomless Coffee Mugs (4 Pack)     |
+-------------------------------------+

This column 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 REGEXP '[0-9]+';

Result:

+-------------------------------------+
| ProductName                         |
+-------------------------------------+
| Smash 2000 Sledge Hammer            |
| Chainsaw (Includes 5 spare fingers) |
| Bottomless Coffee Mugs (4 Pack)     |
+-------------------------------------+

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

In this case, we used MySQL’s REGEXP function to find all rows that matched a pattern. The pattern encompasses all numerical digits from 0 to 9, plus any other character.