how to check value is numeric or not a numeric in mysql select query as follow

Question

value->{1,2,Yes,No,5,6}

select if((value is numeric),value,'not a numeric') as column_name

how to implement this if in my mysql select query

Duplicate question with other great answers here: https://stackoverflow.com/questions/5064977/detect-if-value-is-number-in-mysql Although it was not the accepted answer on that question, the best answer might be this one: https://stackoverflow.com/a/48789594/564664 — drapkin11, Jun 13 '20 at 03:37

score 6 · Answer 1 · edited Jun 29 '15 at 06:39

6

This should do it :)

select if(field REGEXP '^-?[0-9]+$' > 0, field, 'not a numeric') as column_name

Example:

SELECT '12345' REGEXP '^-?[0-9]+$'

Returns: 1 (its a number)

SELECT 'abcdef' REGEXP '^-?[0-9]+$'

Returns: 0 (its NOT a number)

edited Jun 29 '15 at 06:39

Identity1

1,139
16
33

answered Jun 29 '15 at 06:19

Shane Taylor

61
1

score 3 · Answer 2 · answered May 04 '17 at 12:47

This is an old question, but when I needed the same thing the fastest solution was

select if((value *1),value, 'not a number') as column_name;

Granted, this will not consider zero a number, but neither did the Romans and they did ok for thousands of years. For me, given the improved speed across the hundreds of millions of rows, this was the best solution.

score 0 · Answer 3 · answered Jun 29 '15 at 06:35

You can try it-

SELECT IF(table_column*1>0,table_column,'Not a Numeric') 
FROM your_table;

Note: If there is a value "9 hello" then it will be treat as numeric as 9 is numeric value, if you need to exclude it also then revert. But in your question there is no such value.

how to check value is numeric or not a numeric in mysql select query as follow

3 Answers3