Entry

How can I select only positive numbers from a varchar field?

How can I select only numbers from a varchar field?

Jan 25th, 2000 06:37
Nathan Wallace, Sinisa Milivojevic, Stefano Ricci

Assume you have a table like this:

    CREATE TABLE foo (
        a VARCHAR(20)
    )

    INSERT INTO foo (a) VALUES('hello');
    INSERT INTO foo (a) VALUES('55');

To select only the rows that contain a number greater than zero do:

    SELECT * FROM foo WHERE a 0 > 0;

To get rows with a = positive, negative or even zero try this:

    SELECT * FROM foo WHERE a 0<>0 or a regexp "^0(.0 )?$"