CODEWITHSUNDEEP
mysqlregex
Sailormoon
Sailormoon

Reputation: 267

mysql how to recognize between pure number and mixed number/letter without a function?

In MySQL database, I have a column in a table that has both pure numbers or mix number/letters. There's no pattern, and I want to distinguish if it's pure number, and flag it as true, otherwise false. Is there a good method I can use? I tried:

ID REGEXP '^[[:digit:]]+$',
ID REGEXP '[0-9]+',
   REGEXP '^[a-zA-Z.]+$',
   REGEXP '[^0-9]*1[^0-9]*2[^0-9]*3[^0-9]*4[^0-9]*5[^0-9]*'

Column:

2A0000BY
4A00A0BF.1
12345678
12345679.1
300000BE
123456FD
3S435F40

Upvotes: 1

Views: 111

Answers (2)

RoboBear
RoboBear

Reputation: 5764

You could try using a CASE statement along with regexp during a SELECT statement.

Something like this might work: it returns true/1 for every "id" entry that is only numbers, and false/0 otherwise:

SELECT
  id,
  CASE 
     WHEN id regexp '^[0-9.]*$' THEN true
     ELSE false
  END AS is_only_numbers
FROM strings;

SQL Fiddle Demonstrating

Upvotes: 0

Taher A. Ghaleb
Taher A. Ghaleb

Reputation: 5240

What about using something like the following to match numbers. If not, it means it has some letters:

ID REGEXP '^[\-]?[0-9]+\\.?[0-9]*$'

You can also do that without using REGEX, like this:

CONCAT('',ID * 1) = ID

You can test the full queries for both approaches here.

Hope it helps.

Upvotes: 1

Related Questions