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-28 08:03:17Z

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, 2015 at 8:03

answered Dec 28, 2015 at 7:49

Simo Erkinheimo

1,40711 silver badges18 bronze badges

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

5 Comments

MeltingDog Over a year ago

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?

Simo Erkinheimo Over a year ago

If the current answer doesn't cover that case, use this: REGEXP ('^[0-9]+$')

MeltingDog Over a year ago

Ah, weird, Its picking up some HTML I have stored in the table (specifically, Google Map iframes)

Simo Erkinheimo Over a year ago

@MeltingDog Sorry I just edited the comment above. Try the new one.

MeltingDog Over a year ago

yep, perfect! Thanks!

Community · Accepted Answer · 2017-05-23 10:28:19Z

0

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.

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered Dec 28, 2015 at 7:50

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related