CODEWITHSUNDEEP
mysqlsqlnumericvarchar
Wes
Wes

Reputation: 904

MySQL - Select only numeric values from varchar column

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

Upvotes: 33

Views: 93224

Answers (6)

Sreejith N
Sreejith N

Reputation: 41

List item has string continue with numbers

$string = "Test";

select * from table where columnname REGEXP "$string-*[0-9]+";

Upvotes: 1

Jit
Jit

Reputation: 41

SELECT * FROM mixedvalues 
WHERE value > 0 
ORDER BY CAST(value as SIGNED INTEGER) ASC

Upvotes: 1

user5577526
user5577526

Reputation: 15

You can filter your result set using the ISNUMERIC function:

SELECT value
FROM #mixedvalues 
where ISNUMERIC(value)=1

Upvotes: -4

Slowcoder
Slowcoder

Reputation: 2120

SELECT * 
FROM mixedvalues 
WHERE concat('',value * 1) = value;

Reference: Detect if value is number in MySQL

Upvotes: 9

Patrick Q
Patrick Q

Reputation: 6393

You were close :

SELECT * 
FROM mixedvalues 
WHERE value > 0;

SQLFiddle

Upvotes: 4

Strawberry
Strawberry

Reputation: 33945

SELECT * 
FROM mixedvalues 
WHERE value REGEXP '^[0-9]+$';

Upvotes: 111

Related Questions