CODEWITHSUNDEEP
mysqlselectnumberswhere-clause
Peter
Peter

Reputation: 1330

How can I SELECT rows that are not number

How can I select rows in MySQL that are not numbers?

 SELECT * 
 FROM `TABLE`
 WHERE `Column_B` <> Number

I know, this is a stupid question, but I haven't found a solution for this. Column_B is varchar(3).

Upvotes: 2

Views: 9009

Answers (3)

guido
guido

Reputation: 19194

This is one way of doing that, using mysql regexp operator (fiddle):

SELECT * FROM `TABLE`
WHERE `Column_B` NOT REGEXP '^-?[0-9]*\\.?[0-9]+([Ee][+-][0-9]+)?$';

Supports signed/unsigned integers, decimals, and scientific notation.

Note: it is recommended to avoid using sql keywords for entity names, even if you quote them with backticks.

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

this also will work for you

   SELECT col  FROM Table1 
  WHERE col NOT IN ( SELECT col FROM Table1
                   where CAST(col as DECIMAL(10,5)) !=0
                   or Col = '0' )

DEMO

Upvotes: -2

Nisar
Nisar

Reputation: 6038

Try this sqlfiddle..

http://sqlfiddle.com/#!2/17f28/1

SELECT * FROM Table1 WHERE col NOT REGEXP '^[0-9]+$'

If you want to match real numbers (floats) rather than integers, you need to handle the case above, along with cases where your pattern is between 0 and 1 (i.e. 0.25), as well as case where your pattern has a decimal part that is 0. (i.e. 2.0). And while we're at it, we'll add support for leading zeros on integers (i.e. 005):

 ^0*[1-9][0-9]*(\.[0-9]+)?|0+\.[0-9]*[1-9][0-9]*$

sql looks like SELECT * FROM Table1 WHERE col NOT REGEXP '^0*[1-9][0-9]*(\.[0-9]+)?|0+\.[0-9]*[1-9][0-9]*$'

Upvotes: 7

Related Questions