CODEWITHSUNDEEP
mysqlsql
TheLettuceMaster
TheLettuceMaster

Reputation: 15734

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

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.

Upvotes: 0

Views: 6483

Answers (4)

BlitZ
BlitZ

Reputation: 12168

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

Upvotes: 3

jacouh
jacouh

Reputation: 8741

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:]\.]*$';

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

try this

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

demo here

Upvotes: 4

Seymour
Seymour

Reputation: 7067

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

Upvotes: 0

Related Questions