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.

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. — Aaron, Mar 12 '13 at 18:14

score 265 · Accepted Answer · edited Feb 15 '23 at 03:03

265

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.

edited Feb 15 '23 at 03:03

Ryan M

18,333
31
67
74

answered Mar 12 '13 at 18:09

Taryn

242,637
56
362
405

1

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; – Jonathan Sayce Jun 04 '14 at 12:26
1

@JonathanSayce It is bad practice to not use a length, I suggest reading [Bad habits to kick : declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) by [Aaron Bertrand](http://stackoverflow.com/users/61305/aaron-bertrand) – Taryn Jun 04 '14 at 12:37
Interesting post - thanks @bluefeet - I'd assumed in the cast/convert scenario it would have used the size it needed rather than something arbitrary. – Jonathan Sayce Jun 04 '14 at 12:43
@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. – Taryn Jun 04 '14 at 12:49
@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 – Kushal Suthar Jun 19 '14 at 08:01
@kushPHP I would suggest that you create a new question with the details needed to solve it. – Taryn Jun 19 '14 at 12:15
@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. – Kushal Suthar Jun 20 '14 at 06:12
@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"*](https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat)? – Pacerier Apr 08 '15 at 09:02
3

@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. – Taryn Apr 08 '15 at 12:19
@bluefeet, How do you handle having to guess a particular length for the char? – Pacerier Apr 18 '15 at 13:01
@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. – Taryn Apr 18 '15 at 13:09
Added SQL Fiddle directly to response. Hopefully Stack Overflow will have a lightweight SQL code snippet plugin in the future. – Mr. Polywhirl Dec 21 '16 at 16:05
@Mr.Polywhirl That's totally fine, I know SQL Fiddle has some reliability issues. – Taryn Dec 21 '16 at 16:06
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. – Gerard ONeill Aug 23 '17 at 15:43
@bluefeet What if my field is char(1) but i want to convert it into enum('m','f') ?? – dinesh kandpal Sep 17 '17 at 18:00
if I have an int column with value 65 and convert it to char, will it be 'A' or "65" (0x36 0x35)? – SparK Feb 25 '19 at 14:29

score 45 · Answer 2 · edited Sep 25 '22 at 07:37

45

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.

edited Sep 25 '22 at 07:37

Christos Lytras

36,310
4
80
113

answered Mar 12 '13 at 18:08

Aaron

55,518
11
116
132

not sure because I use mysql but it seems as though SQL allows varchar http://stackoverflow.com/a/11989599 though your answer is correct for mysql, thanks. – CrandellWS Aug 25 '14 at 19:55
@Aaron What if my field is char(1) but i want to convert it into enum('m','f') ?? – dinesh kandpal Sep 17 '17 at 17:59
why is it char and not varchar? – Collin Feb 08 '23 at 16:31

score 23 · Answer 3 · edited Sep 12 '15 at 18:19

23

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;

edited Sep 12 '15 at 18:19

Mad Physicist

107,652
25
181
264

answered Sep 12 '15 at 18:08

nancy

231
2
2

2

Hey, SELECT concat(id, '') FROM some_table; is a good hack for MySQL, thank you! – Charles Cavalcante Apr 21 '16 at 14:53
1

Yeah, that's it – George G Mar 05 '20 at 16:19

score 3 · Answer 4 · answered Mar 13 '13 at 06:24

3

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.

answered Mar 13 '13 at 06:24

Andrew Lazarus

18,205
3
35
53

1

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 – AdrianBR Sep 15 '15 at 07:30

score 2 · Answer 5 · edited Mar 16 '18 at 21:54

2

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

edited Mar 16 '18 at 21:54

H.B

75
1
8

answered May 18 '16 at 01:51

user6348455

21
1

score 0 · Answer 6 · answered Mar 16 '18 at 21:01

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))...

score 0 · Answer 7 · answered Apr 03 '20 at 17:25

0

Should be able to do something like this also:

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

answered Apr 03 '20 at 17:25

John Drinane

1,279
2
14
25

score -1 · Answer 8 · answered Mar 12 '13 at 18:08

-1

use :

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

answered Mar 12 '13 at 18:08

user2132046

95
1
2
7

Cast int to varchar

8 Answers8

Linked

Related