MySQL - Select only numeric values from varchar column
79,401
Solution 1
SELECT *
FROM mixedvalues
WHERE value REGEXP '^[0-9]+$';
Solution 2
SELECT *
FROM mixedvalues
WHERE concat('',value * 1) = value;
Reference: Detect if value is number in MySQL
Solution 3
You were close :
SELECT *
FROM mixedvalues
WHERE value > 0;
Solution 4
SELECT * FROM mixedvalues
WHERE value > 0
ORDER BY CAST(value as SIGNED INTEGER) ASC
Solution 5
List item has string continue with numbers
$string = "Test";
select * from table where columnname REGEXP "$string-*[0-9]+";
Author by
Wes
Updated on August 02, 2022Comments
-
Wes almost 2 years
Consider the following table :
create table mixedvalues (value varchar(50)); insert into mixedvalues values ('100'), ('ABC100'), ('200'), ('ABC200'), ('300'), ('ABC300'), ('400'), ('ABC400'), ('500'), ('ABC500');
How can I write a select statement that would only return the numeric values like
100 200 300 400 500
SQLFiddle