• Status: Solved
• Priority: Medium
• Security: Public
• Views: 396

# 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'

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

0
Internet_Engineer
• 2
1 Solution

Commented:
try this :
and always add ',' at end of @MyId

---------------------
DECLARE @MyId AS VARCHAR (500)
SET @MyId = '7,17,107,177,'

SELECT CHARINDEX(cast(Id as varchar), @MyId ), * FROM Person
WHERE
@MyId IS NULL OR @MyId = '' OR
( CHARINDEX(cast(Id as varchar), @MyId )>0 and
substring(@MyId,CHARINDEX(cast(Id as varchar), @MyId ) + len(cast(Id as varchar)),1) = ',')

--------------------
0

Commented:

DECLARE @MyId AS VARCHAR (500)
SET @MyId = '7,17,107,177,'

WHERE
@MyId IS NULL OR @MyId = '' OR
( CHARINDEX(cast(Id as varchar), @MyId )>0 and
substring(@MyId,CHARINDEX(cast(Id as varchar), @MyId ) + len(cast(Id as varchar)),1) = ',')
0

Author Commented:
It works
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.