Cast int to varchar
MysqlSqlDatabaseCastingMariadbMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
You will need to cast
or convert
as a CHAR
datatype, there is no varchar
datatype that you can cast/convert data to:
select CAST(id as CHAR(50)) as col1
from t9;
select CONVERT(id, CHAR(50)) as colI1
from t9;
See the following SQL — in action — over at SQL Fiddle:
/*! Build Schema */
create table t9 (id INT, name VARCHAR(55));
insert into t9 (id, name) values (2, 'bob');
/*! SQL Queries */
select CAST(id as CHAR(50)) as col1 from t9;
select CONVERT(id, CHAR(50)) as colI1 from t9;
Besides the fact that you were trying to convert to an incorrect datatype, the syntax that you were using for convert
was incorrect. The convert
function uses the following where expr
is your column or value:
CONVERT(expr,type)
or
CONVERT(expr USING transcoding_name)
Your original query had the syntax backwards.
Solution 2 - Mysql
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
.
Solution 3 - Mysql
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;
Solution 4 - Mysql
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.
Solution 5 - Mysql
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
Solution 6 - Mysql
use :
SELECT cast(CHAR(50),id) as colI1 from t9;
Solution 7 - Mysql
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))...
Solution 8 - Mysql
Should be able to do something like this also:
Select (id :> VARCHAR(10)) as converted__id_int
from t9