The data types varchar and varchar are incompatible in the modulo operator SQL Server

I get this error:

The data types varchar and varchar are incompatible in the modulo operator.

when running this code

' Number LIKE '%' + @search + '%' ';


The number is an integer and this code snippet appears enclosed in ''; due to a dynamic pivot statement. Any ideas how i can run this? thanks

WHERE Foo LIKE '%bar%'

3 Answers
3

Presumably, you have something like this:

SELECT ' Number LIKE '%' + @search + '%' ';


If so, you have single quotes inside a string. So the "string" is interpreted as:

SELECT (' Number LIKE ') % (' + @search + ') % (' ');


The % is the modulo operator . . . and hence your error.

%

In order to put single quotes in a string, you need to double them up. Something like this:

SELECT ' Number LIKE ''%'' + @search + ''%'' ';


Are you sure you need those outer quotes, should it not just be

Number LIKE '%' + @search + '%'


Otherwise you'll need to escape the inner quotes. Try this:

'Number LIKE ''%'' + @search + ''%'''


Because % is a modulo operator in SQl Server and it is interpreted as such because to insert quto in a string you need to double it as other answers suggest.

%

Other solution would be:

'CHARINDEX(' + @search + ', Number) > 0'


I always tend to use that over LIKE oprator with %someString%. Both determine if there is string within the other.

LIKE

%someString%

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.