charindex in SQL doesn't give the desired result

Question

I have a string which is a output from a function eg: "1,3,16,..,..". I used the following sql query and ran it in the query builder in VS and it didn't give me any syntax errors.

SELECT ItemID, Name, RelDate, Price, Status FROM item_k WHERE (ItemID = cast(charindex(',',@itemIDs) as int))

I gave 3,16 as the @itemID parameter values, but it didn't give the desired results.

Then I used the following SQL query (without charindex):

SELECT ItemID, Name, RelDate, Price, Status FROM item_k WHERE (ItemID = @itemIDs)

I gave 3 as the @itemID parameter value, and I got a result for it. I also gave 16 (on a separate occasion) as the @itemID parameter value, and I got a result for it. Concludes that there are values for ItemID 3 & 16.

Why doesn't SQL query with charindex give me any result?

I can't seem to figure out the issue here, please help.

Thanks.

Answer 1

CHARINDEX just returns the postition where the character is found within the string.

So when @ItemIDs is set to '3,16' then your WHERE clause...

WHERE (ItemID = CAST(CHARINDEX(',', @ItemIDs) AS INT))

...is equivalent to...

WHERE ItemID = 2

...because CHARINDEX returns 2 since the comma character is found at position 2 of the string '3,16'.

I'm guessing that (a) you don't have a row in your table where ItemID is 2, and (b) you don't really want the position of the comma to dictate which rows are returned.

Answer 2

Try SELECT ItemID, Name, RelDate, Price, Status FROM item_k WHERE ItemID in (@itemIDs)

Answer 3

You can create a query dynamically that uses the in operator:

declare @Sql varchar(1000)
set @Sql = 'select ItemID, Name, RelDate, Price, Status from item_k where ItemID in (' + @itemIDs + ')'
exec(@Sql)

Be careful with what you send into the procedure, though. As with any dynamic SQL, if the data comes from user input without validation, the procedure is wide open for SQL injection.

Edit:
This is what happens in the query:

First we declare a variable to hold the dynamic query. This is just a varchar variable that is large enough.

In the variable we put the @itemIDs variable between two strings to form the query. The comma separated values is put between the parentheses of the in operator to form an expression similar to: where ItemID in (1,3,16)

Finally the exec command executes the query in the variable.

Answer 4

Here's yet another solution. In my experience, when you have a list of ItemIds as a string of comma separated values, you need a split function. This is very useful to have.

With a split function, you can simply do an INNER JOIN with the results of calling the split function and passing the list of ItemIds and associated delimeter as follows:

DECLARE @ItemIDs varchar(100)
SET @ItemIDs = '1,3,16,22,34,35'

SELECT 
     ItemID, Name, RelDate, Price, Status 
FROM item_k
     INNER JOIN dbo.UTILfn_Split(@ItemIDs,',') itemIds 
       ON itemIds.Value = item_k.ItemID

While this may look complicated at first, it is the more elegant and maintainable solution. Here's the code for creating the dbo.UTILfn_Split function. You need to run this first:

IF EXISTS (SELECT * FROM sysobjects WHERE id = 
object_id(N'[dbo].[UTILfn_Split]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[UTILfn_Split]
GO

CREATE FUNCTION dbo.UTILfn_Split
(
    @String nvarchar (4000),
    @Delimiter nvarchar (10)
)
RETURNS @ValueTable TABLE ([Value] nvarchar(4000))
BEGIN
 DECLARE @NextString nvarchar(4000)
 DECLARE @Pos int
 DECLARE @NextPos int
 DECLARE @CommaCheck nvarchar(1)

 --Initialize
 SET @NextString = ''
 SET @CommaCheck = RIGHT(@String,1)  
 --Check for trailing Comma, if not exists, INSERT
 --if (@CommaCheck <> @Delimiter )
 SET @String = @String + @Delimiter

 --Get position of first Comma
 SET @Pos = CHARINDEX(@Delimiter,@String)
 SET @NextPos = 1

 --Loop while there is still a comma in the String of levels
 WHILE (@pos <>  0)  
 BEGIN
  SET @NextString = SUBSTRING(@String,1,@Pos - 1)

  INSERT INTO @ValueTable ( [Value]) Values (@NextString)

  SET @String = SUBSTRING(@String,@pos +1,LEN(@String))

  SET @NextPos = @Pos
  SET @pos  = CHARINDEX(@Delimiter,@String)
 END 
 RETURN
END