database blue

How to Detect Whether a Value Has At Least 1 Numeric Digit in SQL?

If we want to search through database and find columns which have at least 1 numeric value then here is the method to do so. Here we are talking about numeric digits, not text or other symbols. So if anyone wants to understand the method to detect whether a value has at least 1 numeric digit then just follow through this tutorial and by just following this method you can easily tackle similar problems in big databases without any hassle.

SQL Server

In SQL server we can use the LIKE operator.

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

Here using “LIKE” operator we can get “ProductName” has at least one numeric digit in it.

Oracle

In Oracle, we has REGEXP_LIKE which helps us to find numeric value using regular expression pattern.

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

“REGEXP_LIKE” complies with the POSIX regular expression standard so we can use this query as well.

SELECT ProductName 
FROM Products
WHERE REGEXP_LIKE(ProductName, '[[:digit:]]');

MySQL and MariaDB

In MySQL and MariaDB we can use “REGEXP”.

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

In MySQL we can also use POSIX:

SELECT ProductName 
FROM Products
WHERE ProductName REGEXP '[[:digit:]]';

PostgreSQL

Here is the PostgreSQL query.

SELECT ProductName 
FROM Products
WHERE ProductName ~ '[0-9]+';

and of course with “POSIX”.

SELECT ProductName 
FROM Products
WHERE ProductName ~ '[[:digit:]]';

SQLite

In SQLite we can use “REGEX” operator.

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

And here is the query with “REGEXP()” function.

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

I hope this might help. If you have any questions please write to us in comments section.