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: stackoverflow.com/questions/5064977/… Although it was not the accepted answer on that question, the best answer might be this one: stackoverflow.com/a/48789594/564664drapkin11– drapkin112020-06-13 03:37:23 +00:00Commented Jun 13, 2020 at 3:37
Add a comment
|
3 Answers
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)
Comments
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.
1 Comment
TerryE
Neat, thanks. In my case I wanted to group by, summing a column if that value was valid. so
sum(if(value*1,value,0)) as "Sum" worked fine.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.
Comments
