Charindex in SQL does not give the desired result
I have a string that is the result of a function, for example: "1,3,16, .., ..".
I used the following SQL query and ran it in the Query Builder in Visual Studio 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 values for the @itemID parameter, but didn't give the results I wanted.
Then I used the following SQL query (no charindex):
SELECT ItemID, Name, RelDate, Price, Status FROM item_k WHERE (ItemID = @itemIDs)
I gave 3 as the value of the @itemID parameter and I got the result for it. I also gave 16 (in a separate case) as the value of the @itemID parameter and I got the result for it. I concluded that there are values for ItemID 3 and 16.
Why is the SQL query with charindex not giving me a result?
I can't seem to figure out this problem, please help.
a source to share
Here's another solution. In my experience, when you have a list of ItemIds as a comma separated string of values, you need the split function. It is very useful to have.
With the split function, you can simply INNER JOIN the results of the split function call and pass a list of ItemIds and its associated delimeter like this:
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 a more elegant and convenient solution. Here's the code to create dbo.UTILfn_Split . You have 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
a source to share
CHARINDEX
just returns a message where the character is inside the string.
So, when @ItemIDs
set to '3,16'
, then your suggestion WHERE
...
WHERE (ItemID = CAST(CHARINDEX(',', @ItemIDs) AS INT))
... is equivalent to ...
WHERE ItemID = 2
... because it CHARINDEX
returns 2
because the comma character is at position 2 of the string '3,16'
.
My guess is that (a) you don't have a row in your table where ItemID
- 2
, and (b) you really don't want the comma position to dictate which rows are returned.
a source to share
You can dynamically create a query that uses the operator in
:
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 to the procedure. As with any dynamic SQL, if the data comes from user input without validation, the procedure is wide open to SQL injection.
Edit:
This is what happens in the request:
First, we declare a variable to hold the dynamic request. It's just a variable varchar
that's big enough.
In the variable, we put the @itemIDs variable between the two lines to form the request. Comma-separated values are placed between the parentheses of the operator in
to form an expression like:where ItemID in (1,3,16)
Finally, the command exec
executes the query in a variable.
a source to share