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

souslov · Accepted Answer · 2015-01-11 11:59:03Z

111

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

edited Jan 11, 2015 at 11:59

souslov

44.7k11 gold badges92 silver badges113 bronze badges

answered Mar 19, 2014 at 15:51

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tom Auger Over a year ago

`^-?[0-9]+$' don't forget possible negative numbers.

Nisha Over a year ago

Does it work if the column stores data like: abc mnop prqs xyz 71.6789 30.7654 ?

Strawberry Over a year ago

@Nisha No. This is a different question

Aqua 4 Over a year ago

how do we enhance it to work for decimal values as well?

Strawberry Over a year ago

@Aqua4 I think that is a question in itself

Community · Accepted Answer · 2017-05-23 12:02:57Z

9

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

Reference: Detect if value is number in MySQL

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Mar 19, 2014 at 15:35

Slowcoder

2,1303 gold badges17 silver badges21 bronze badges

1 Comment

Adriano Over a year ago

This solution works for the moment, but gives you a Warning: #1292

souslov · Accepted Answer · 2015-01-11 11:59:28Z

4

You were close :

SELECT * 
FROM mixedvalues 
WHERE value > 0;

SQLFiddle

edited Jan 11, 2015 at 11:59

souslov

44.7k11 gold badges92 silver badges113 bronze badges

answered Mar 19, 2014 at 15:34

Patrick Q

6,3942 gold badges28 silver badges34 bronze badges

6 Comments

Wes Over a year ago

This is much simpler than the other answer. How does it work?

Strawberry Over a year ago

This relies on the fact that the letters never appear after the numbers

Wes Over a year ago

Ok. Thanks. I noticed it will fail for something like 600ABC.

markthewizard1234 Over a year ago

This isn't a suitable and could create issues later.

John Over a year ago

This answer is incorrect. '1ABCABC' will be returned using this query which is clearly NOT a numeric value.

|

Jit · Accepted Answer · 2019-03-13 11:53:00Z

1

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

answered Mar 13, 2019 at 11:53

Jit

411 silver badge3 bronze badges

Comments

Sreejith N · Accepted Answer · 2020-08-07 07:27:38Z

1

List item has string continue with numbers

$string = "Test";

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

edited Aug 7, 2020 at 7:27

answered Jul 31, 2020 at 7:29

Sreejith N

417 bronze badges

1 Comment

allanlaal Over a year ago

if you wanna search for numbers with certain decimals: ([0-9]){2,3} to match numbers from 10..999

trincot · Accepted Answer · 2015-11-18 17:30:03Z

-4

You can filter your result set using the ISNUMERIC function:

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

edited Nov 18, 2015 at 17:30

trincot

357k38 gold badges282 silver badges338 bronze badges

answered Nov 18, 2015 at 16:16

user5577526

15

2 Comments

Maelish Over a year ago

Question was for mysql.

Aqua 4 Over a year ago

This function does not exist in MYSQL.

Collectives™ on Stack Overflow

MySQL - Select only numeric values from varchar column

SQLFiddle

6 Answers 6

5 Comments

1 Comment

6 Comments

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

1 Comment

6 Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related