-3

hi i have the following query

   SELECT MAX(CAST(SUBSTRING_INDEX(battles,';',1) AS INT)) from battles;

the query work well in my localhost (XAMMP) , But anywhere else it gives me this error

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT)) from battles' at line 1

example : http://sqlfiddle.com/#!9/e19e8c/2

brown34
  • 23
  • 2
  • 1
    check this link: https://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql CAST as INT is invalid – BWS Oct 03 '19 at 15:05
  • Use unsigned rather than INT ,`SELECT MAX(CAST(SUBSTRING_INDEX(battles,';',1) AS unsigned)) from battles;` INT IS INVALID – Aishwarya Oct 03 '19 at 15:17
  • Note that XAMPP is using MariaDB. `CAST('123string' AS INT)` works for MariaDB but not for (oracles) MySQL. Use `CAST('123string' AS SIGNED)`, which works on both systems. – Paul Spiegel Oct 03 '19 at 15:28

1 Answers1

1

INT isn't a valid type for a MySQL CAST. Try SIGNED INTEGER.

https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_convert (CONVERT and CAST support the same types)

robsiemb
  • 6,157
  • 7
  • 32
  • 46