CODEWITHSUNDEEP
sqlcharindex
raymantle
raymantle

Reputation: 121

CHARINDEX with integer

Hi I have a select which works great where I use CHARINDEX on a varchar column, but when I try it on a integer column I get the following error;

Argument data type int is invalid for argument 1 of charindex function.

Below I have listed both queries.

stremail is varchar intEmployeeID in integer

**below returns results as aspected**
SELECT  intEmployeeID
FROM [dbo].[tblEmployees]
WHERE CHARINDEX(',' + strEmail + ',', ',' +  REPLACE('[email protected],  [email protected]', ' ', '') + ',') > 0


**below I get an error if I'm trying to select on a integer column**
SELECT  intEmployeeID
FROM [dbo].[tblEmployees]
WHERE CHARINDEX(',' + intEmployeeID + ',', ',' +  REPLACE('1, 2', ' ', '') + ',') > 0

Any help would be most greatful.

Upvotes: 0

Views: 3652

Answers (3)

Matt
Matt

Reputation: 802

SELECT  intEmployeeID
FROM [dbo].[tblEmployees]
WHERE CHARINDEX(',' + cast(intEmployeeID as nvarchar(max)) + ',', ',' +  REPLACE('1, 2', ' ', '') + ',') > 0

Upvotes: 1

D Stanley
D Stanley

Reputation: 152626

You have to explicitly convert the integer to a character string, otherwise it compiles the + operators as integer addition instead of string concatenation :

CHARINDEX(',' + CONVERT(VARCHAR(10),intEmployeeID) + ',', ',' +  REPLACE('1, 2', ' ', '') + ',')

Upvotes: 1

Akshey Bhat
Akshey Bhat

Reputation: 8545

+ cast(intEmployeeID as varchar)+

Cast intEmployeeID as varchar

Upvotes: 1

Related Questions