sql like operator on integer

I google search 'sql like operator on integer', all tell me to use

   where cast ( int_field as text ) like '%30.%'

Warning: reality is opposite position!!!

why I can use 'like' operator on integer, decimal, directly, without cast?

here is my mysql example:

another example:

integer type

Solution 1:

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)