CODEWITHSUNDEEP
mysqlcastingintvarchar
Jyot Khangura Chahal
Jyot Khangura Chahal

Reputation: 11

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.

Upvotes: 1

Views: 16708

Answers (3)

spencer7593
spencer7593

Reputation: 108370

"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

Upvotes: 4

minhhq
minhhq

Reputation: 464

You can use CONCAT(user_follow.object_id)

Upvotes: 1

Blank
Blank

Reputation: 12378

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.

Upvotes: 1

Related Questions