When working with MariaDB, you may occasionally find yourself needing to return all rows that contain at least one numerical digit.

In such cases, you may find the following example useful.

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            |
| Bottomless Coffee Cup               |
| Smash 2000 Sledgehammer             |
| Chainsaw (includes 3 spare fingers) |
| Bottomless Coffee Mugs (4 Pack)     |
+-------------------------------------+

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 REGEXP '[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.

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