sql like operator on integer

by

This is handled by an implicit type conversion in MySQL.

When you use a numeric type in a string context, i.e. comparing it to a string, then the numeric is implicitly converted to a string, then the comparison is evaluated. You don’t need to use CAST() first.

This is documented for MySQL here: https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

I don’t know what references you found while googling. They could be describing the SQL implementation in a different RDBMS product. Implementations vary between vendors; not all SQL databases have the same behavior.

For example, MySQL doesn’t even support CAST(<expr> AS TEXT). You can CAST(<expr> AS CHAR) (see https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast). So the example you read was not even possible for MySQL.

Or it could be that you’re reading an article or a tutorial written by someone with an incomplete understanding of SQL semantics.

In general, implicit type conversions are standard in SQL, so you should find similar behavior in similar implementations.

https://crate.io/docs/sql-99/en/latest/chapters/03.html says:

Numeric Operations

A number is compatible with, and comparable to, all other numbers –
that is, all numbers are mutually comparable and mutually assignable.
Numbers may not be directly compared with, or directly assigned to,
any other class, though implicit type conversions can
occur in expressions, SELECTs, INSERTs, DELETEs and UPDATEs. Explicit
numeric type conversions can be forced with the CAST operator.

(emphasis mine)

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. Using LIMIT within GROUP BY to get N results per group?
  3. Create a Cumulative Sum Column in MySQL
  4. How to perform grouped ranking in MySQL
  5. Equivalent of explode() to work with strings in MySQL
  6. Required to join 2 tables with their FKs in a 3rd table
  7. MySQL Trigger after update only if row has changed
  8. MySQL “WITH” clause
  9. mysql select dynamic row values as column names, another column as value
  10. Ordering by specific field value first
  11. MySQL “Group By” and “Order By”
  12. How to change the CHARACTER SET (and COLLATION) throughout a database?
  13. MySQL Query to select data from last week?
  14. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  15. Simple Random Samples from a Sql database
  16. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  17. How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?
  18. Are there disadvantages to using a generic varchar(255) for all text-based fields?
  19. How do I use properly CASE..WHEN in MySQL
  20. Getting all parent rows in one SQL query
  21. Delete query not working in mysql
  22. How to insert data to MySQL with auto-incremented column(field)?
  23. Replace Into Query Syntax
  24. When to use a left outer join?
  25. MySQL 5.6 – DENSE_RANK like functionality without Order By
  26. SQL (MySQL) vs NoSQL (CouchDB) [closed]
  27. DELETE FROM `table` AS `alias` … WHERE `alias`.`column` … why syntax error?
  28. MySQL update statement to store ranking positions
  29. How to convert a varchar column type to date type without losing the dates
  30. MySQL Multiple Joins in one query?

Leave a Comment