[mysql] Cast int to varchar

I have below query and need to cast id to varchar

Schema

create table t9 (id int, name varchar (55));
insert into t9( id, name)values(2, 'bob');

What I tried

select CAST(id as VARCHAR(50)) as col1 from t9;

select CONVERT(VARCHAR(50),id) as colI1 from t9;

but they don't work. Please suggest.

I solved a problem to comparing a integer Column x a varchar column with

where CAST(Column_name AS CHAR CHARACTER SET latin1 ) collate latin1_general_ci = varchar_column_name

use :

SELECT cast(CHAR(50),id) as colI1 from t9;

Yes

SELECT id || '' FROM some_table;
or SELECT id::text FROM some_table;

is postgresql, but mySql doesn't allow that!

short cut in mySql:

SELECT concat(id, '') FROM some_table;

Should be able to do something like this also:

Select (id :> VARCHAR(10)) as converted__id_int
from t9 

I don't have MySQL, but there are RDBMS (Postgres, among others) in which you can use the hack

SELECT id || '' FROM some_table;

The concatenate does an implicit conversion.

I will be answering this in general terms, and very thankful to the above contributers.
I am using MySQL on MySQL Workbench. I had a similar issue trying to concatenate a char and an int together using the GROUP_CONCAT method. In summary, what has worked for me is this:

let's say your char is 'c' and int is 'i', so, the query becomes:
...GROUP_CONCAT(CONCAT(c,' ', CAST(i AS CHAR))...

You're getting that because VARCHAR is not a valid type to cast into. According to the MySQL docs (http://dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_cast) you can only cast to:

• BINARY[(N)]
• CHAR[(N)]
• DATE
• DATETIME
• DECIMAL[(M[,D])]
• SIGNED
• [INTEGER]
• TIME
• UNSIGNED [INTEGER]

I think your best-bet is to use CHAR.