0

Hi I'm trying to do a simple update query based on a value in another column e.g

UPDATE tbl1 SET col2=1 WHERE col1 < 10

Where col1 contains the following values:

1,2,3....19,20,N1,N2...N9

But the query is bombing out when it hits a value with a non numeric char in it. I've tried:

UPDATE tbl1 SET col2=1 WHERE CAST(col1 AS UNSIGNED INT) < 10

Not surprisingly that didn't work either but I would have thought that in the first example when checking if N1 < 10 it would have ignored the row instead of failing...?

How could I do this?

doovers
  • 8,545
  • 10
  • 42
  • 70
  • is the prefix `N` constant? – John Woo Aug 27 '13 at 05:11
  • Here's how to check if something is Numeric in MySql: http://stackoverflow.com/questions/75704/how-do-i-check-to-see-if-a-value-is-an-integer-in-mysql – dcaswell Aug 27 '13 at 05:13
  • I tried an `UPDATE` query like yours, it didn't bomb out when it encountered `N1`, it just updated that row. When converting a string to a number, if it doesn't begin with a number it converts to 0, which is less than 10. – Barmar Aug 27 '13 at 05:20
  • @Barmar Hmmm I'm getting **#1292 - Truncated incorrect INTEGER value: 'F3'** with the 2nd example I gave...?? – doovers Aug 27 '13 at 05:29
  • Yeah, I've read about that error, but I've never gotten it myself. – Barmar Aug 27 '13 at 05:31

1 Answers1

0

you can do it using dirty REPLACE()

WHERE CAST(REPLACE(REPLACE(REPLACE(REPLACE(col1, 'M', ''), 'F', ''), 'N', ''), 'L', '') AS UNSIGNED) < 10

with this procedure, the query isn't really good in terms of performance but will suit your needs.

John Woo
  • 258,903
  • 69
  • 498
  • 492