Geek asks and answers

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

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

SELECT * FROM mytable WHERE mycolumn IS UNSIGNED INTEGER

So, strings abc and numbers like '12 .3 'and '12 .0' will not match , but integers like '123'.

Where mycolumn is text of type / varchar

+3
source share
4 answers
SELECT * FROM mytable WHERE mycolumn REGEXP '^[0-9]+$'

Shouldn't be simple enough?

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

Updated example

EDIT

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

select * from myTable 
where cast(cast(myColumn as decimal(8,2)) as signed) = 
      cast(myColumn as decimal(8,2))
+2
source 
SELECT * FROM mytable WHERE mycolumn = FLOOR(mycolumn)

//edit: , , . 12.3, 12 12.0 ( ).

, - , .

0
source 
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
0
source

More articles:


All Articles