Cast int to varchar

Question

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.

Solution 1

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

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

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

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

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

View more solutions

Comments

[Aaron]

Next time be sure to include the actual error message that you are seeing. That usually helps a lot with figuring-out what is wrong. This time you were lucky that many of us just happen to know what's going on here.

[Jonathan Sayce]

Might be worth mentioning that you don't have to provide the length - both cast and convert will allow something along the lines of select CAST(id as CHAR) as col1 from t9;

[Taryn]

@JonathanSayce It is bad practice to not use a length, I suggest reading Bad habits to kick : declaring VARCHAR without (length) by Aaron Bertrand

[Jonathan Sayce]

Interesting post - thanks @bluefeet - I'd assumed in the cast/convert scenario it would have used the size it needed rather than something arbitrary.

[Taryn]

@JonathanSayce I'm not overly familiar with the ins & outs of MySQL and it might not use something arbitrary, but I wouldn't rely on the MySQL engine to be that smart (no offense to MySQL). In order to be sure you get the proper length, I would always explicitly provide one.

[Kushal Suthar]

@bluefeet i am using your concept for cast the column with UNION nut i face the error #1271 - Illegal mix of collations for operation 'UNION' please help me out

[Taryn]

@kushPHP I would suggest that you create a new question with the details needed to solve it.

[Kushal Suthar]

@bluefeet No thanx i solve it using CONVERT(ID USING 'utf8'). i take first table column as text and second table column as int so that why i face this kind of problem.

[CrandellWS]

not sure because I use mysql but it seems as though SQL allows varchar stackoverflow.com/a/11989599 though your answer is correct for mysql, thanks.

[Pacerier]

@bluefeet, Instead of guessing a particular length for our char, why not use concat(int_column)'s implicit "int-to-string" conversion whereby "A numeric argument is converted to its equivalent binary string form"?

[Taryn]

@Pacerier IMO, using concat() to do a conversion isn't necessarily that intuitive. I'd prefer my code to be clear and that just doesn't make a whole lot of sense.

[Pacerier]

@bluefeet, How do you handle having to guess a particular length for the char?

[Taryn]

@Pacerier Since they are attempting to convert an int in the question, then you know the max # of digits that could be included so use that. Most people tend to know the max length of the value then are converting.

[AdrianBR]

Postgres has the clear and concise casting though, "::data_type". Would be a shame not to use the proper way here. Mysql's concat or +0 though are justified

[Charles Cavalcante]

Hey, SELECT concat(id, '') FROM some_table; is a good hack for MySQL, thank you!

[Mr. Polywhirl]

Added SQL Fiddle directly to response. Hopefully Stack Overflow will have a lightweight SQL code snippet plugin in the future.

[Taryn]

@Mr.Polywhirl That's totally fine, I know SQL Fiddle has some reliability issues.

[Gerard ONeill]

I suggest the reason you don't have to use a length for CHAR is that MySQL will size it for you. Perhaps this is why they didn't need it to support varchar. No need to exclude one functionality from MySQL (char without a length) just to depend on another (concat). As for hardcoding the length.. Why, unless you want or need to. Otherwise your code will have char(65536) all over the place. Or you tie it to the table definition, which means two places to update when it changes.

[dinesh kandpal]

@Aaron What if my field is char(1) but i want to convert it into enum('m','f') ??

[dinesh kandpal]

@bluefeet What if my field is char(1) but i want to convert it into enum('m','f') ??

[SparK]

if I have an int column with value 65 and convert it to char, will it be 'A' or "65" (0x36 0x35)?

[George G]

Yeah, that's it