Is there any way to convert varchar to int value in sql?

Question

I have a query like this when I pass the values into in operator in sql it shows:

Conversion failed when converting the varchar value '3,4,9' to data type int.

How can I solve the issue?

declare @values varchar(100)
set @values = '3,4,9'
select @values
select * from CmnItemType where ItemTypeID in (@values)

Does this answer your question? [Passing a varchar full of comma delimited values to a SQL Server IN function](https://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) — Eric Brandt, Nov 19 '19 at 17:17
To answer the title of your question: Yes, you can convert a `VarChar` value, e.g. `'349'`, to an `Int`: `Cast( '349' as Int )`. What integer value do you expect from `'3,4,9'`? — HABO, Nov 19 '19 at 18:18

score 3 · Answer 1 · answered Nov 19 '19 at 16:25

3

No. You can use string_split() or a similar user-defined function:

where itemtypeid in (select try_convert(int, value) from string_split(@values))

answered Nov 19 '19 at 16:25

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · edited Mar 22 '20 at 11:53

What I usually do is use table variable, like this one:

DECLARE @values TABLE (id INT)
INSERT INTO @values (id) VALUES (3),(4),(9)
SELECT id FROM @values

From that, you could simply do a join to your tables.

If you are creating a stored procedure, you can use a TVP to pass parameters, here is the Microsoft doc on that. With a TVP, your code can simply call your SP with a list and you will be able to join it in the SP.

Hope this will help.

Is there any way to convert varchar to int value in sql?

2 Answers2