MySQL - Select only numeric values from varchar column

Wes picture Wes · Mar 19, 2014 · Viewed 68.1k times · Source

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

Answer

Strawberry picture Strawberry · Mar 19, 2014
SELECT * 
FROM mixedvalues 
WHERE value REGEXP '^[0-9]+$';