MySQL: Update a column if value is numeric only

Question

Is there a way to update a column in mysql if value is number only?

ie. UPDATE myTable SET ColumnA =NewValue WHERE ColumnA isnumeric

Column A is varchar(25) and Column A value may be text or 1 or 2 or 23...

thank u

Answer 1

Use REGEXP() function

Try this:

UPDATE myTable SET ColumnA = NewValue WHERE ColumnA REGEXP '^[0-9]+$'

OR

UPDATE myTable SET ColumnA = NewValue WHERE ColumnA REGEXP '^[[:digit:]]+$'

Answer 2

Try to create INT column and make call:

update myTable set IntColumn = ColumnA

than replace intcolumn type to varchar and update your table where ColumnA = IntColumn (Not real numeric strings will be empty or different from real numeric).

Answer 3

Try this :

UPDATE myTable SET ColumnA =NewValue WHERE concat('',ColumnA * 1) = ColumnA 

Or

UPDATE myTable SET ColumnA =NewValue WHERE ColumnA REGEXP '[0-9]+';

Look at this topic Detect if value is number in MySQL

Answer 4

Try something like this. It works for me

if ('value_to_check' REGEXP '^[0-9]+$') {
THEN 
// Do ur work,..
}

Answer 5

Use regular expression to see if your column contains number or anything else

WHERE ColumnA REGEXP '[0-9]+'