How to exclude non-numeric column results from MySQL query?

Question

I have a column, called value, that includes strings as well as numbers as data, eg:

contentid, value
16, 200
18, 150
47, Foo
16, Red
16, 50
18, GREEN

I need a way to retrieve only the results that are actual numbers (and, additionally, that are <= 180).

The expected results from above should be: 18, 150 and 16, 50 but I am getting results with strings as well.

I have tried this from other SO questions:

SELECT * 
FROM  `contentvalues` 
WHERE (
contentid =16
OR contentid =18
)
AND `value` <= 180
AND value NOT LIKE  '%[a-z0-9]%'

But this has not worked.

Would anyone be able to point me in the right direction?

Simo Erkinheimo · Accepted Answer · 2015-12-28T08:03:17.973

3

According to this:

... AND value REGEXP ('[0-9]')

... but someone's blog is never the best source. Actually that matches everything that contains a number. Better would be

... AND value REGEXP ('^[0-9]+$')

Above regex is

^      "From the begining of the string..."
[0-9]  "There should be a number..."
+      "actually exactly 1 or more of those numbers"
$      "and the the string should just end."

edited Dec 28 '15 at 08:03

answered Dec 28 '15 at 07:49

Simo Erkinheimo

1,347
9
17

Thanks! Sorry - should have put it above - but would you know of anyway to exclude values that contain both numbers and letters as well? Eg: RED123? – MeltingDog Dec 28 '15 at 07:53
1

If the current answer doesn't cover that case, use this: REGEXP ('^[0-9]+$') – Simo Erkinheimo Dec 28 '15 at 07:54
Ah, weird, Its picking up some HTML I have stored in the table (specifically, Google Map iframes) – MeltingDog Dec 28 '15 at 07:57
@MeltingDog Sorry I just edited the comment above. Try the new one. – Simo Erkinheimo Dec 28 '15 at 07:57
yep, perfect! Thanks! – MeltingDog Dec 28 '15 at 08:00

score 0 · Answer 2 · edited May 23 '17 at 10:28

Here is a solution which does not require the REGEXP() function, which may not available in all RDBMS:

SELECT * 
FROM  `contentvalues` 
WHERE (contentid = 16 OR contentid =18)
    AND concat('',value * 1) = value

This will work as long as the numbers in the value column do not appear in scientific notation, with trailing decimals, or other weird formats. Read this SO article for more information.

How to exclude non-numeric column results from MySQL query?

2 Answers2