Solved

# CHARINDEX Chooses wrong result

Posted on 2011-03-21
Medium Priority
394 Views
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
Question by:Internet_Engineer
• 2

LVL 3

Accepted Solution

ssisworo earned 2000 total points
ID: 35182697
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

LVL 3

Expert Comment

ID: 35182781

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 Closing Comment

ID: 35182829
It works
0

## Featured Post

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.