7

I have a problematic script that ran, so i need to select all rows that contain values that is not a straight up integer. How do I do this? ISSTRING was on a Google search but I get an error from it saying ISSTRING does not exist. :(

So far I've muddled with:

SELECT id, area, city FROM `homes` WHERE ISSTRING(`area`) OR ISSTRING(`city`)

With no luck whatsoever...

jeffkee
  • 5,106
  • 12
  • 44
  • 76
  • You can do it via regular expressions - http://dev.mysql.com/doc/refman/5.1/en/regexp.html – Brian Hoover May 01 '12 at 22:37
  • 6
    See this: http://stackoverflow.com/questions/75704/how-do-i-check-to-see-if-a-value-is-an-integer-in-mysql. You'd only have to add a `NOT`. – bfavaretto May 01 '12 at 22:38
  • 1
    If `area` and `city` are supposed to be of type integer only, then change the table definition so that it allows only integers (i.e. smallint, bigint, int, etc). All non-integer values will be changed to 0 I believe. – Buttle Butkus May 01 '12 at 23:27
  • Yup changing fields as soon as I fix this.. realized teh error AFTER teh data dump was done. – jeffkee May 01 '12 at 23:34

1 Answers1

9

RegExp works, but what I would do is check to see if abs() is equal to zero AND the field is not zero. I think this will be faster than RegExp.

SELECT id, area, city FROM `homes` WHERE (abs(area) = 0 AND area != '0') OR (abs(city) = 0 AND city != '0')
Reza S
  • 9,480
  • 3
  • 54
  • 84