CODEWITHSUNDEEP
mysql
lisovaccaro
lisovaccaro

Reputation: 33956

SQL SELECT rows with numbers on column?

Possible Duplicate: How to only select numeric data from mysql?

I have a column which actually houses strings (type varchar) is there a way to select rows that only hold numbers in them?

E.G:

COLUMN1 COLUMN2 COLUMN3
1234    dassa   dasasfa
aaaa    fdsa    dsfa 
4dsf4   dfssa   aaddss

The query should return

1234    dassa   dasasfa

Upvotes: 0

Views: 782

Answers (4)

Mohammed Shannaq
Mohammed Shannaq

Reputation: 835

You can do it by this query in mysql

select * from st where COLUMN1 REGEXP '^[0-9]+$';

the result will be

COLUMN1     COLUMN2     COLUMN3
1234        dassa       dasasfa

Upvotes: 1

barryhunter
barryhunter

Reputation: 21091

where column1 regexp '^[[:digit:]]+$'

I think should do it.

Another idea:

where case(round(column,0) as char) = column1

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270617

Use regular expression matching with REGEXP()

SELECT COLUMN1, COLUMN2, COLUMN3
FROM tbl
WHERE COLUMN1 REGEXP '^[0-9]+$';

Upvotes: 0

Phil
Phil

Reputation: 164776

SELECT * FROM `table` WHERE `COLUMN1` REGEXP '^[[:digit:]]+$'

See http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp

Upvotes: 2

Related Questions