I have a Mysql query. I want to filter only integer result. My query is-

我有一个Mysql查询。我想只过滤整数结果。我的询问是 -

SELECT * FROM table as p WHERE p.test between 0 AND 999

But result comes this-

但结果来了 -

I want to ask two things-

我想问两件事 -

1)Is there any way to exclude below result-

1）有没有办法排除以下结果 -

FO4001
FO4002

2)Why are these coming in the result?

2）为什么这些会出现在结果中？

1 个解决方案

Try this one, use REGEXP to test if the value is all numeric.

试试这个，使用REGEXP测试值是否全部为数字。

SELECT * 
FROM table1 
WHERE x BETWEEN 0 AND 999
      AND x REGEXP '^[0-9]+$';

SQLFiddle Demo

Try this one, use REGEXP to test if the value is all numeric.

试试这个，使用REGEXP测试值是否全部为数字。

SELECT * 
FROM table1 
WHERE x BETWEEN 0 AND 999
      AND x REGEXP '^[0-9]+$';