query="SELECT * FROM Table WHERE `VarCharCol1`= numeric"

Question

I have a table with varchar column, this column contains both numeric records and AlphaNumeric records, but require only the ones that have no letters. How can I achieve this?

Sorry, that doesn't make much sense? Can you clarify your question please. — BenM, Dec 10 '13 at 14:01
Its usually pretty bad practice to do this, you should really ensure that if you want something in a particular format you set the column with this type — Melbz, Dec 10 '13 at 14:05
possible duplicate of [Detect if value is number in MySQL](http://stackoverflow.com/questions/5064977/detect-if-value-is-number-in-mysql) — M Khalid Junaid, Dec 10 '13 at 14:06

score 3 · Accepted Answer · answered Dec 10 '13 at 14:04

3

Try this:

SELECT * FROM table WHERE `varcharcol1` RLIKE '^[0-9]+$'

answered Dec 10 '13 at 14:04

Aziz Shaikh

16,245
11
62
79

This should do the job after fixing it. – Rolice Dec 10 '13 at 14:06
@ВикторПеров oops sorry, thanks for the pointer – Aziz Shaikh Dec 10 '13 at 14:07
Both REGEXP and RLIKE seem to work. What is the difference? – Cor Cool Dec 10 '13 at 15:09
RLIKE is a synonym for REGEXP. Reference: http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp – Aziz Shaikh Dec 11 '13 at 08:39

score 2 · Answer 2 · answered Dec 10 '13 at 14:04

2

tryed

SELECT column_name FROM table WHERE ISNUMERIC(column_name) = 1

answered Dec 10 '13 at 14:04

user2805926

21
2

This doesn't work: FUNCTION ISNUMERIC does not exist – Cor Cool Dec 10 '13 at 15:12

score 2 · Answer 3 · answered Dec 10 '13 at 14:06

2

select * from table where concat('', column * 1) = column;

Returns array of data, where column is numeric (contains only digits).

answered Dec 10 '13 at 14:06

Victor Perov

1,697
18
37

WOW, this is an interesting one, strange, but logical. And it actually works! – Cor Cool Dec 10 '13 at 15:18

query="SELECT * FROM Table WHERE `VarCharCol1`= numeric"

3 Answers3