33

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

Improve this question

6 Answers 6

Reset to default
111 
SELECT * FROM mixedvalues WHERE value REGEXP '^[0-9]+$';
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

`^-?[0-9]+$' don't forget possible negative numbers.
Does it work if the column stores data like: abc mnop prqs xyz 71.6789 30.7654 ?
@Nisha No. This is a different question
how do we enhance it to work for decimal values as well?
@Aqua4 I think that is a question in itself
9 
SELECT * FROM mixedvalues WHERE concat('',value * 1) = value;

Reference: Detect if value is number in MySQL

Improve this answer

1 Comment

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

You were close :

SELECT * FROM mixedvalues WHERE value > 0;

SQLFiddle

Improve this answer

6 Comments

This is much simpler than the other answer. How does it work?
This relies on the fact that the letters never appear after the numbers
Ok. Thanks. I noticed it will fail for something like 600ABC.
This isn't a suitable and could create issues later.
This answer is incorrect. '1ABCABC' will be returned using this query which is clearly NOT a numeric value.
|
1 
SELECT * FROM mixedvalues WHERE value > 0 ORDER BY CAST(value as SIGNED INTEGER) ASC
Improve this answer

Comments

1

List item has string continue with numbers

$string = "Test"; select * from table where columnname REGEXP "$string-*[0-9]+";
Improve this answer

1 Comment

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

You can filter your result set using the ISNUMERIC function:

SELECT value FROM #mixedvalues where ISNUMERIC(value)=1
Improve this answer

2 Comments

Question was for mysql.
This function does not exist in MYSQL.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.