how to solve Error converting data type varchar to bigint. the queries is select ID from Table1 wher-CodePudding

I'm trying out thìs solution using queries:

select ID from Table1 where '0123456789' like '%'   ID   '%'

and the follow error show up:

Msg 8114, Level 16, State 5, Line 3 Error converting data type varchar to bigint.

What did I do wrong?

I expected it return ID with consecutive value.

CodePudding user response：

The error is likely coming from the LIKE expression (no pun intended). Try this version which explicitly casts the ID to text:

SELECT ID
FROM Table1
WHERE '0123456789' LIKE '%'   CAST(ID AS varchar(12))   '%';

I speculate that the varchar to bigint error is happening because one of SQL Server's attempts to make the LIKE work is casting the '%' string literals to big integers, to match the type of the ID column. Obviously, that won't work.