'Converting int to varchar data type in MySQL

I have two tables. I need to compare two values but they are in different datatype in both the tables. In one table, it is int and in another table it is varchar. I tried using varchar with cast(), but cast() does not work with varchar.

My query is like following:

select user.username, user_follow.object_id from user,user_follow
left join user_follow
ON cast(user_follow.object_id as varchar(25))= user.username
WHERE user.username= cast(user_follow.object_id as varchar(25));

Help please. thanks.

mysqlcastingintvarchar


Solution 1:[1]

"does not work" is not a precise description of the observed behavior.

We expect MySQL to "work" according to the specification. The behavior we expect is for MySQL to return a specific error message, such as

Error Code: 1064
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 VARCHAR

We expect MySQL to return that error message because according to the MySQL Reference Manual, VARCHAR is not a valid type for CAST, but CHAR is a valid type.

Reference:

http://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#function_convert

NOTE:

There are some other problems with the query.

There appear to be two references to the user_follow table. At least one of those references will need to have a table alias assigned.

The query is also using old-school comma syntax for a join operation, mixed with a join operation is using the newer JOIN keyword.

Without a specification of the resultset to be returned, we can only guess at which queries will satisfy the requirements.

  SELECT u.username
       , f.object_id
    FROM user u
    LEFT
    JOIN user_follow f 
      ON CONVERT(CHAR(25),f.object_id) = u.username

Solution 2:[2]

You should cast as a CHAR datatype, there is no varchar datatype that you can cast data to, anyway try this please:)
Not sure what you want to use, left join or join.

select u.username, uf.object_id from user u
left join user_follow uf
ON cast(uf.object_id as char(25))= cast(u.username as char(25))
-- WHERE cast(u.username as char(25)) = cast(uf.object_id as char(25));

Or

select u.username, uf.object_id from user u, user_follow uf
-- left join user_follow uf
-- ON cast(uf.object_id as char(25))= cast(u.username as char(25))
WHERE cast(u.username as char(25)) = cast(uf.object_id as char(25));

And take a look of function_cast.

Solution 3:[3]

You can use CONCAT(user_follow.object_id)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 minhhq

Related Questions