MySQL - Select only numeric values from varchar column

Question

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

SQLFiddle

score 111 · Accepted Answer · edited Jan 11 '15 at 11:59

111

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

edited Jan 11 '15 at 11:59

potashin

44,205
11
83
107

answered Mar 19 '14 at 15:51

Strawberry

33,750
13
40
57

8

`^-?[0-9]+$' don't forget possible negative numbers. – Tom Auger Jan 14 '16 at 22:00
Does it work if the column stores data like: abc mnop prqs xyz 71.6789 30.7654 ? – Nisha Jul 11 '20 at 14:56
@Nisha No. This is a different question – Strawberry Jul 11 '20 at 15:28
how do we enhance it to work for decimal values as well? – Aqua 4 Jul 27 '20 at 08:47
@Aqua4 I think that is a question in itself – Strawberry Jul 27 '20 at 09:11

score 9 · Answer 2 · edited May 23 '17 at 12:02

9

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

Reference: Detect if value is number in MySQL

edited May 23 '17 at 12:02

Community

1
1

answered Mar 19 '14 at 15:35

Slowcoder

2,060
3
16
21

This solution works for the moment, but gives you a `Warning: #1292` – Adriano Dec 09 '20 at 08:09

score 4 · Answer 3 · edited Jan 11 '15 at 11:59

4

You were close :

SELECT * 
FROM mixedvalues 
WHERE value > 0;

SQLFiddle

edited Jan 11 '15 at 11:59

potashin

44,205
11
83
107

answered Mar 19 '14 at 15:34

Patrick Q

6,373
2
25
34

This is much simpler than the other answer. How does it work? – Wes Mar 19 '14 at 15:38
2

This relies on the fact that the letters never appear after the numbers – Strawberry Mar 19 '14 at 16:19
4

Ok. Thanks. I noticed it will fail for something like 600ABC. – Wes Mar 19 '14 at 16:22
This isn't a suitable and could create issues later. – markthewizard1234 Dec 01 '15 at 10:59
This answer is incorrect. '1ABCABC' will be returned using this query which is clearly NOT a numeric value. – John Nov 17 '18 at 00:25
Not only that, but all those non-convertible values will cause warnings. That's not a problem unless you're running in strict-mode.... – Nancy Deschênes Mar 07 '19 at 21:36

score 1 · Answer 4 · answered Mar 13 '19 at 11:53

1

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

answered Mar 13 '19 at 11:53

Jit

31
1
3

Sreejith N · Answer 5 · 2020-08-07T07:27:38.183

1

List item has string continue with numbers

$string = "Test";

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

edited Aug 07 '20 at 07:27

answered Jul 31 '20 at 07:29

Sreejith N

25
5

if you wanna search for numbers with certain decimals: ([0-9]){2,3} to match numbers from 10..999 – allanlaal Nov 21 '20 at 19:07

score -4 · Answer 6 · edited Nov 18 '15 at 17:30

-4

You can filter your result set using the ISNUMERIC function:

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

edited Nov 18 '15 at 17:30

trincot

317,000
35
244
286

answered Nov 18 '15 at 16:16

user5577526

15

1

Question was for mysql. – Maelish Jan 22 '16 at 13:20
This function does not exist in MYSQL. – Aqua 4 Jul 27 '20 at 06:01

MySQL - Select only numeric values from varchar column

SQLFiddle

6 Answers6

Linked

Related