How to select all rows where mycolumn is an integer value?

Question

What would be a good way to select all the rows from a table where a column is an unsigned integer, for example here is some pseudo-sql

SELECT * FROM mytable WHERE mycolumn IS UNSIGNED INTEGER

So that strings 'abc' and numbers like '12.3' and '12.0' would not match, but integers like '123' would.

Where mycolumn is type text/varchar

@YuriKolovsky: The question states "column is an unsigned integer". — weenoid, Apr 27 '12 at 08:12
@weenoid in that case the question would be useless, all values will be positive integers. The OP wants to `select all rows where`, where `where` is a SQL clause :) — Florin Ghita, Apr 27 '12 at 08:16
@YuriKolovsky: Please see my updated answer. Is there any reason why you can't just set the datatype of mycolumn to be an integer? — weenoid, Apr 27 '12 at 08:16
Do you need 12.0 matched or not? And are all values in column numeric or is can contain non numeric data also? — elevener, Apr 27 '12 at 08:17
@weenoid change the datatype of the table column? no, it contains other number based values, which I'm trying to filter out. — Timo Huovinen, Apr 27 '12 at 08:20
See [this question](http://stackoverflow.com/questions/75704/how-do-i-check-to-see-if-a-value-is-an-integer-in-mysql) — eggyal, Apr 27 '12 at 08:23

score 9 · Accepted Answer · edited Apr 27 '12 at 08:35

9

SELECT * FROM mytable WHERE mycolumn REGEXP '^[0-9]+$'

Shouldn't that be simple enough?

edited Apr 27 '12 at 08:35

Timo Huovinen

53,325
33
152
143

answered Apr 27 '12 at 08:24

Robin Castlin

10,956
1
28
44

Try [0-9] instead of \d. Might be due to backslash. – Robin Castlin Apr 27 '12 at 08:29
It does not match 12.0. Dunno is it bug or a feature. – Agent_L Apr 27 '12 at 08:40
@Agent_L explain, what should I update in the question? I updated it, don't know if I did it like you thought that I should? – Timo Huovinen Apr 27 '12 at 08:53
@YuriKolovsky The rejection of 12.0 is very important, but it's not easy to find in comments. The title suggests it's about integer VALUE, not writing convention. – Agent_L Apr 27 '12 at 09:02

juergen d · Answer 2 · 2012-04-27T11:21:10.193

2

select * from mytable  
where cast(mycolumn as int) = mycolumn

Updated Example

EDIT

I did not see you have a varchar column. Try this instead:

select * from myTable 
where cast(cast(myColumn as decimal(8,2)) as signed) = 
      cast(myColumn as decimal(8,2))

edited Apr 27 '12 at 11:21

answered Apr 27 '12 at 08:14

juergen d

201,996
37
293
362

I made a sample table mytable and tried it and got: `ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int) = mycolumn' at line 2` – Timo Huovinen Apr 27 '12 at 08:17
1

@YuriKolovsky: Do not write int in `'`. Use `as int` and not `as 'int'` – juergen d Apr 27 '12 at 08:20
@juergend the test should be with `myColumn varchar(30)`. THe OP has at char type column – Florin Ghita Apr 27 '12 at 08:21
now is perfect! +1 from me. However, the OP states in comments that values like 12.0 should be filtered out. IT's not what he initially asked – Florin Ghita Apr 27 '12 at 08:33
@juergend using your exact sql on a test table, where it's using `as int` and `as float` without the single quotes, still getting that error though. – Timo Huovinen Apr 27 '12 at 08:34
@YuriKolovsky: Please post the exact query you are trying and I will have a look. – juergen d Apr 27 '12 at 08:37
@juergend `select * from mytable where cast(cast(mycolumn as float) as int) = cast(mycolumn as float);` – Timo Huovinen Apr 27 '12 at 08:38
@juergend I tried it in sqlfiddle http://sqlfiddle.com/#!3/d2f21/1 and it worked, but fails to do so in my server, instead I get this error, maybe it has something to do with mysql? `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'float) as int) = cast(mycolumn as float)' at line 1` – Timo Huovinen Apr 27 '12 at 09:13
@YuriKolovsky: Yes it is about MySQL. I tried it accidently with SQL Server and not with MYSQL. I am trying to find an answer to the problem. – juergen d Apr 27 '12 at 09:17
@YuriKolovsky: I updated my answer. It works now but not with numbers like `12.0`. You should accept Robin's answer. – juergen d Apr 27 '12 at 11:21
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/10594/discussion-between-yurikolovsky-and-juergen-d) – Timo Huovinen Apr 27 '12 at 11:24

Agent_L · Answer 3 · 2012-04-27T08:45:54.727

0

SELECT * FROM mytable WHERE mycolumn = FLOOR(mycolumn)

//edit: OK, as question is specified, this answer no longer fits. It will reject 12.3 but match 12 AND 12.0 (what turned out to be not indended).

The revised problem can't be solved mathematically, only way is to match characters, as Robin Castlin did.

edited Apr 27 '12 at 08:45

answered Apr 27 '12 at 08:08

Agent_L

4,960
28
30

Would this be true for "12.0"? – Nanne Apr 27 '12 at 08:09
This would result in a value of 12.3 matching a row with an ID of 12 when the OP has stated that this isn't the desired behaviour. – weenoid Apr 27 '12 at 08:12
Yes, it works. If you don't believe me, then try it on demo.phpmyadmin.net as I just did. – Agent_L Apr 27 '12 at 08:23
Apologies, I thought 'FLOOR(mycolumn)' was supposed to represent the value being passed in, not sure why. – weenoid Apr 27 '12 at 08:25

score 0 · Answer 4 · answered Apr 27 '12 at 08:50

0

create table test_int(
test_value varchar(5)
)

insert into test_int values('a')
insert into test_int values('1')
insert into test_int values('12.3')

select *
from test_int 
where isnumeric(
cast(test_value as varchar)) = 1

result

1
12.3

answered Apr 27 '12 at 08:50

Diego

34,802
21
91
134

How to select all rows where mycolumn is an integer value?

4 Answers4

EDIT