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.