UPDATE people
SET name ='numeric'
WHERE ISNUMERIC (name) =1;
Trying to set all names in name column which are numeric values to 'numeric'.
Asked
Active
Viewed 1,102 times
0
-
2And what is the error? – Gordon Linoff Dec 31 '20 at 14:05
-
It is saying function socialdb.ISNUMERIC does not exist. – rohith mudiraj Dec 31 '20 at 14:09
-
`ISNUMERIC` isn't a [tag:mysql] built-in function. Is it possible you're copying some code from [tag:sql-server]? Even if so, it's an almost pointless function there too, answering a question nobody wants to ask. – Damien_The_Unbeliever Dec 31 '20 at 14:11
-
Is there any alternative to check if any numerics are present in column? – rohith mudiraj Dec 31 '20 at 14:13
-
ISNUMERIC doesn't exist in MySQL. You would have to create it. A simple Google search turned up this: https://forums.mysql.com/read.php?60,599995,599995 – Russ Dec 31 '20 at 14:13
-
[Detect if value is numeric in MySQL](https://stackoverflow.com/questions/5064977/detect-if-value-is-number-in-mysql) – lurker Dec 31 '20 at 14:16
2 Answers
1
ISNUMERIC() is a function in SQL Server. I don't even recommend using the function in that database -- the TRY_CAST() and TRY_CONVERT() are more accurate.
In MySQL, you can use regular expressions. It is unclear what you mean by "isnumeric" in this context, but:
UPDATE people
SET name = 'numeric'
WHERE name REGEXP '^[0-9]+[.]?[0-9]*$';
This gets any things with a string of digits, at most one decimal point, and at least one digit before the decimal point. It is easy to modify for negative numbers and exponential notation if that is really what you intend.
If by "numeric" you just mean a string of digits, then:
UPDATE people
SET name = 'numeric'
WHERE name REGEXP '^[0-9]+$';
Gordon Linoff
- 1,242,037
- 58
- 646
- 786
1
You should probably be using the HAVING clause, here. The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
UPDATE people
SET name ='numeric'
HAVING ISNUMERIC (name) =1;
Atom Store
- 961
- 1
- 11
- 35