Internet_Engineer
asked on
CHARINDEX Chooses wrong result
CHARINDEX works most of the time very well, but sometimes it chooses the wrong id. ID is an integer while the search string is varchar
DECLARE @MyId AS VARCHAR (500)
SET @MyId = '7,17,107,177'
SELECT * FROM leads.BatchDetails
WHERE
(@MyId IS NULL OR @MyId = '' OR
CHARINDEX(CAST(Id AS VARCHAR),@MyId)>0)
This will select from the DB '10' because it finds it in '107'
7
10
17
107
177
The problem is id 10
DECLARE @MyId AS VARCHAR (500)
SET @MyId = '7,17,107,177'
SELECT * FROM leads.BatchDetails
WHERE
(@MyId IS NULL OR @MyId = '' OR
CHARINDEX(CAST(Id AS VARCHAR),@MyId)>0)
This will select from the DB '10' because it finds it in '107'
7
10
17
107
177
The problem is id 10
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works
DECLARE @MyId AS VARCHAR (500)
SET @MyId = '7,17,107,177,'
SELECT * FROM Personleads.BatchDetails
WHERE
@MyId IS NULL OR @MyId = '' OR
( CHARINDEX(cast(Id as varchar), @MyId )>0 and
substring(@MyId,CHARINDEX(