SQL select with 'in' casting from string to tinyint

Question

I have a table from which I want to select data. One coloumn 'myCol' has datatype tinyint. It has values from 1 to 8.

In my select I have a variable @myVar with datatype varchar(), that has values like '1,2' or '3,4'.

Now I am trying to do something like this:

select * from myTable
where myCol in (@myVar)

Unfortunately I get the following error:

Conversion failed when converting the varchar value '2,3' to data type tinyint.

How to change the select that it works like it should?!

It's very important to keep the select performance as high as possible!

juergen d · Accepted Answer · 2013-09-24T08:08:32.430

1

Since you only have values from 1 to 8 you can use a string search method. Something like

select * from myTable
where CHARINDEX(cast(mycol as varchar), @myVar) > 0

SQLFiddle demo

edited Sep 24 '13 at 08:08

answered Sep 24 '13 at 07:52

juergen d

201,996
37
293
362

score 0 · Answer 2 · edited May 23 '17 at 12:11

0

If you create a function similar to the accepted answer in Splitting of comma separated values, you will then be able to do:

select * from myTable t
    inner join dbo.fnSplitStringAsTable(@myVar, ',') s on t.myCol = s.Value

Note that I'm assuming SQL Server, based on your syntax and the error message.

edited May 23 '17 at 12:11

Community

1
1

answered Sep 24 '13 at 07:55

Adrian Wragg

7,311
3
26
50

Thanks for your answer. Let's suggest the table is very big. Is it a performance killer to split the string in a seperated table and do a inner join? Thanks for the help? – steven Sep 24 '13 at 08:01
@dotnetsharp It shouldn't kill performance at all, no. It's not as compact as the Juergen's answer, but may be faster due to fewer CASTs being required. Best answer is to try it, and finding out - I'd be interested to know for myself. – Adrian Wragg Sep 24 '13 at 08:10

score 0 · Answer 3 · answered Sep 24 '13 at 08:17

Change your data to contain flagged enum

[Flags]
public enum ContactMethod
{
  Value1 = 1,
  Value2 = 2,
  Value3 = 4,
  Value4 = 8,
  Value5 = 16,
  Value6 = 32,
  Value7 = 64,
  Value8 = 128
}


select * from mytable where myCol | 3 = 3

Will then return you all rows with values 1 or 2

SQL select with 'in' casting from string to tinyint

3 Answers3

SQLFiddle demo