-1

If I have a table like

create table test
(
    id    varchar(255) not null
        primary key,
    price varchar(10)  not null,
    link  varchar(255) not null
);

and it has this data:

id price link
1 50 text
2 50 text
3 50 USD text

and I run a query like update test set link = 'a' where price <= 50;

mysql gives me an error [22001][1292] Data truncation: Truncated incorrect DOUBLE value: '50 USD'

But if I change the column length of price to varchar(255) suddenly the query does not throw an error and works (sort of) updating ALL the columns, including the one where price is 50 USD. Which is odd.

Why? is this a setting? Is this a glitch in mysql? The cutoff for the column length for when it errors vs when it doesn't seems to be completely arbitrary and different on different databases

Lehren
  • 99
  • 2
  • 11
  • The actual length that will not generate an error is 64; I'm not a Mysql expert but would guess this has something to do with implicit casting. Regardless, you are comparing an integer with a string - don't do that, compare apples with apples so it should be `<= '50'` - of course this won't work the way you expect it to because you should not be storing a price as a varchar. – Stu May 08 '23 at 08:33
  • Yes well, we allow users to create arbitrary tables and load their own data in it, so we have to treat everything as text since we cannot be sure that a column that should contain integers, does only contain integers. – Lehren May 08 '23 at 08:47
  • And a column length of 64 still generates an error for me. The cutoff seems to be 85 in my case – Lehren May 08 '23 at 08:54
  • You can see it on this [DB Fiddle](https://dbfiddle.uk/mN15p75f), perhaps it's ambiguous behaviour dependent on version; regardless, even if it did not cause an error you *cannot* compare strings as if they were numbers and expect it to compare as if they were numerical values. – Stu May 08 '23 at 09:07

1 Answers1

0

This is a way to do it by extracting digits only using REGEXP_SUBSTR

update test set link = 'a' where REGEXP_SUBSTR(price,"[0-9]+") <= 50;

Demo here

SelVazi
  • 10,028
  • 2
  • 13
  • 29
  • I don't want to extract the digits though. It should work on columns that only contain digits. That also includes doubles such as '50.00' and '50,00' . – Lehren May 08 '23 at 08:45
  • Using `REGEXP_SUBSTR` works .. Which mysql version are you using ? – SelVazi May 08 '23 at 08:48
  • Ah you edited your comment, I only tried the casting to unsigned. But see my updated comment and my question is not specifically about how to solve this, but why mysql gives inconsistent results based on the column length. – Lehren May 08 '23 at 08:50
  • I have added two suggestions :) when I tested, I found that only the solution with REGEXP_SUBSTR is working – SelVazi May 08 '23 at 08:54