IN operator only returning first match

Question

IN operator only returning first match

DerbyNeal

Hall of Fame

Points: 3854
More actions
November 3, 2010 at 8:19 am

#230629

Hi,
I have a problem with some code in my query. I'm using an IN operator as a filter (within a WHERE clause) but the code is only finding a match against the first value found (eur).
The statement reads
AND fs.Currency IN (SELECT item FROM dbo.ABN_fnSplitChar(@Currency,','))
I've also tried
AND fs.Currency IN (SELECT item FROM dbo.ABN_fnSplitChar(@Currency,',') WHERE RTRIM(item) = fs.Currency)
where @Currency = 'eur,gbp'
The dbo.ABN_fnSplitChar function is:
USE [XX]
GO
/****** Object: UserDefinedFunction [dbo].[ABN_fnSplitChar] Script Date: 11/03/2010 14:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ABN_fnSplitChar]
(
@listNVARCHAR(4000),
@DelimiterNVARCHAR(10) = ','
)
RETURNS @tablevalues TABLE (item VARCHAR(20))
AS
BEGIN
DECLARE @P_itemNVARCHAR(4000)
WHILE (LEN(@list) > 0)
BEGIN
IF CHARINDEX(@Delimiter,@List) > 0
BEGIN
SELECT @p_Item = SUBSTRING(@List,1,(CHARINDEX(@Delimiter,@List)-1))
SELECT @List = SUBSTRING(@List,(CHARINDEX(@Delimiter,@List) + LEN(@Delimiter)),LEN(@List))
END
ELSE
BEGIN
SELECT @p_Item = @List
SELECT @List = NULL
END
INSERT INTO @tablevalues (item)
SELECT item = CAST(LTRIM(RTRIM(@p_Item)) AS VARCHAR(20))
END
RETURN
END
The function returns:
(item)
EUR
GBP
..But I'm only getting matches against 'eur' using the above code. If I run the code where @Currency = 'gbp' I get the results for this value.
Any ideas please?
Thanks in advance,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 1

Looks to me like it ought to work.

Any possibility you're using a case-sensitive collation on the database (or that column) that would cause 'gbp' and 'GBP' to not match?

Rob Schripsema
Propack, Inc.

GSquared SSC Guru Points: 260824 More actions · Answer 2

I'd have to see the rest of the query to be able to help much.

As an aside, take a look at uses of a Numbers or Tally table to split a string, instead of the UDF you've got. They're much more efficient. But that won't fix this problem.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

WayneS SSC Guru Points: 95461 More actions · Answer 3

GSquared (11/3/2010)
I'd have to see the rest of the query to be able to help much.
As an aside, take a look at uses of a Numbers or Tally table to split a string, instead of the UDF you've got. They're much more efficient. But that won't fix this problem.

If you use the DelimitedSplit8K function (Click here for the latest Delimited Split Function), your code would then be:

SELECT *

FROM someTable fs

JOIN dbo.DelimitedSplit8K(@Currency, ',') ds

ON fs.Currency = ds.Item

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 4

AND fs.Currency IN (SELECT item FROM dbo.ABN_fnSplitChar(@Currency,','))

Maybe the 'GBP' row(s) are being excluded by another condition ... this does start with "AND" 🙂

Scott Pletcher, SQL Server MVP 2008-2010