CODEWITHSUNDEEP
mysqlregex
John
John

Reputation: 13729

MySQL REGEXP Letters and numbers but no records of only numbers

How do I SELECT all records with rows which the value column contains numbers and letters but not just numbers?

Let's say I have the following MySQL table:

+----+-------+
| id | value |
+----+-------+
| 1  | a1    |
+----+-------+
| 2  | aa    |
+----+-------+
| 3  | 22    |
+----+-------+
| 4  | 3t    |
+----+-------+

The desired id rows returned would be 1, 2 and 4; not 3 because it only contains numbers.

Upvotes: 0

Views: 1429

Answers (3)

Rick James
Rick James

Reputation: 142298

The value/1 trick has a problem -- it only looks at digits at the start of value.

I think you need

WHERE `value` REGEXP '[[:alpha:]]'
  AND `value` REGEXP '[[:digit:]]'

or maybe

WHERE     `value` REGEXP '[[:alpha:]]'    -- contains letters
  AND NOT `value` REGEXP '^[[:digit:]]+$' -- but not just digits

Upvotes: 2

zambonee
zambonee

Reputation: 1647

Use REGEXP:

SELECT * FROM [table] WHERE [value] REGEXP '[A-Za-z]' AND [value] IS NOT NULL

Upvotes: 0

John
John

Reputation: 13729

This did the trick without the need for any regular expressions.

SELECT * FROM table WHERE (value / 1)='NULL';

Upvotes: 0

Related Questions