The following SQLite example returns all rows that contain at least one numerical digit.

Sample Data

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

SELECT ProductName 
FROM Products;

Result:

ProductName                         
------------------------------------
Widget Holder (holds 5 gram widgets)
Widget Opener                       
Bob's "Best" Widget                 
Blue Widget                         
Urban Dictionary Version 1.2        
Beer Water (375ml)                  

Example Query

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                         
------------------------------------
Widget Holder (holds 5 gram widgets)
Urban Dictionary Version 1.2        
Beer Water (375ml)                  

Only those rows that contain numbers are returned. By “numbers”, I mean numerical digits. Numbers can also be represented by words or symbols, but this example only detects numerical digits.

In SQLite, the REGEXP operator is a special syntax for the REGEXP() user function.

Therefore, we can use the following code to get the same result:

SELECT ProductName 
FROM Products
WHERE REGEXP('[0-9]+', ProductName);

Result:

ProductName                         
------------------------------------
Widget Holder (holds 5 gram widgets)
Urban Dictionary Version 1.2        
Beer Water (375ml)