How to cast varchar into int - SQL Error: '=' cannot be applied to varchar, bigint

Question

I have a list of product collection ids in the form of varchar datatype.

Referring to a sample structure below.. I would like to retrieve the item names of collection id (pro_col_id) 3453, 3454, 3455.

My query is as follows:

select pro_col_id, name
from collections_table 
where pro_col_id in (3453, 3454, 3455);

When I ran this query, I got an error

SQL Error: '=' cannot be applied to varchar, bigint

So I tried to cast pro_col_id as int like this:

select pro_col_id, name
from collections_table 
where cast(pro_col_id as int) in (3453, 3454, 3455);

but I still get the same error. Does anyone know what is the issue causing this?

Thank you!

Does this answer your question? [Cast from VARCHAR to INT - MySQL](https://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql) — Martin, Mar 15 '20 at 10:25
[specifically, here](https://stackoverflow.com/a/12127022/3536236) — Martin, Mar 15 '20 at 10:26

score 2 · Answer 1 · answered Mar 15 '20 at 11:12

If the values are strings, then the comparisons should be strings:

select pro_col_id, name
from collections_table 
where pro_col_id in ('3453', '3454', '3455');

If this does not fix your problem, then the issue is not the where clause. One possibility is that collections_table is not really a table but is a view, and something is amiss in the view.

How to cast varchar into int - SQL Error: '=' cannot be applied to varchar, bigint

1 Answers1