Checking rows that are not numbers in a varchar column for MySQL Query

Question

In a perfect world for this type of setup, we would have an integer column that expects only numbers;

But what if you have a varchar column and you want to add a WHERE clause that said something like this:

WHERE <value> is NOT a number

In essence, you are selecting all rows that contain any characters that are NOT ONLY numbers.

This is for MySQL.

This should answer your question [Detect if value is number in MySQL](http://stackoverflow.com/a/5065007/560299) — Ibu, Oct 07 '13 at 17:16

score 4 · Accepted Answer · answered Oct 07 '13 at 17:39

4

try this

    SELECT * FROM myTable WHERE concat('',col1 * 1) != col1

demo here

answered Oct 07 '13 at 17:39

echo_Me

37,078
5
58
78

It SEEMS regular expressions made more sense but we decided to go this route. It's simple, and returns what we need. – TheLettuceMaster Oct 07 '13 at 17:50

BlitZ · Answer 2 · 2013-10-07T17:48:35.443

3

REGEXP or RLIKE are your friends:

SELECT * FROM `MyTable` WHERE `Column` RLIKE '^[^0-9]*$';

UPDv1:

You may use different regexes to detect negative integers:

SELECT
'-2' RLIKE '^[^0-9]*$', -- fails
'-1' RLIKE '-[0-9]';    -- succeeds

For example:

SELECT * FROM `MyTable` WHERE `Column` RLIKE '-[0-9]' OR `Column` RLIKE '^[^0-9]*$';

Tested with this:

SELECT 
    *
FROM
    (
        SELECT 'abs 12 x' as `Column`
        UNION ALL
        SELECT 12
        UNION ALL
        SELECT -2
        UNION ALL
        SELECT '-x'
    ) as `sub`
WHERE
    `Column` RLIKE '-[0-9]'
    OR
    `Column` RLIKE '^[^0-9]*$';

Output:

-2
-x

edited Oct 07 '13 at 17:48

answered Oct 07 '13 at 17:38

BlitZ

12,038
3
49
68

How would you do this to allow Negative values such as "-2" be allowed? – TheLettuceMaster Oct 07 '13 at 17:39
@KickingLettuce You need to allow negative numbers? – BlitZ Oct 07 '13 at 17:42

jacouh · Answer 3 · 2013-10-07T17:47:53.917

0

This would approach somehow your goal:

SELECT * FROM MyTable WHERE NOT MyTable.Field1 REGEXP '^[[:digit:]]*$';

As Field1 is VARCHAR, this will select all rows that Field1 is not wholly numerical.

If you have floating-point value:

SELECT * FROM MyTable WHERE NOT MyTable.Field1 REGEXP '^[[:digit:]\.]*$';

edited Oct 07 '13 at 17:47

answered Oct 07 '13 at 17:29

jacouh

8,473
5
32
43

score 0 · Answer 4 · answered Oct 07 '13 at 17:30

0

You should be able to use a regular expression in the where clause.
The following mysql documentation link provides details:
http://dev.mysql.com/doc/refman/5.1/en/regexp.html

answered Oct 07 '13 at 17:30

Seymour

7,043
12
44
51

Checking rows that are not numbers in a varchar column for MySQL Query

4 Answers4