Selecting integer values rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Selecting integer values rows

Post by anjanesh »

Is there a MySQL equivalent of PHP's is_numeric ?
Im looking for a function to select integer-value rows only. The field type is varchar though and contains alpha-numeric values too.

Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

use casting or regexp.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Code: Select all

SELECT `id` , `col1`FROM `tbl1`WHERE CONVERT(`col1` , UNSIGNED INTEGER) = `col1`
is returning all the values - including ones with alhpa-numeric.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

mysql> SELECT CAST('five' AS SIGNED), CAST(CAST('five' AS SIGNED) AS CHAR) = 'five';
+------------------------+-----------------------------------------------+
| CAST('five' AS SIGNED) | CAST(CAST('five' AS SIGNED) AS CHAR) = 'five' |
+------------------------+-----------------------------------------------+
|                      0 |                                             0 |
+------------------------+-----------------------------------------------+
1 row in set, 2 warnings (0.02 sec)

mysql> SELECT CAST('3' AS SIGNED), CAST(CAST('3' AS SIGNED) AS CHAR) = '3';
+---------------------+-----------------------------------------+
| CAST('3' AS SIGNED) | CAST(CAST('3' AS SIGNED) AS CHAR) = '3' |
+---------------------+-----------------------------------------+
|                   3 |                                       1 |
+---------------------+-----------------------------------------+
1 row in set (0.00 sec)
STRCMP() could also be used.
Post Reply