Solved

TSQL "IN" fails when parameter is comma separated

Posted on 2011-03-18
4
478 Views
Last Modified: 2012-05-11
SELECT with IN clause failes when values are comma separated....

DECLARE @MYVAR AS Varchar(50)
SET @MYVAR = 'FL, AK, NY, CA'

SELECT * FROM MYTABLE
WHERE
(@MYVAR IS NULL OR [State] = @MYVAR)
0
Comment
Question by:Internet_Engineer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35168518
Try this way

DECLARE @MYVAR AS Varchar(50)
SET @MYVAR = 'FL, AK, NY, CA'

SELECT * FROM MYTABLE
WHERE @MYVAR IS NULL
OR [State] IN (@MYVAR)
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 35168553
or

Try this way

DECLARE @MYVAR AS Varchar(50)
SET @MYVAR = 'FL, AK, NY, CA'

SELECT * FROM MYTABLE
WHERE @MYVAR IS NULL
OR CHARINDEX([State], @MYVAR, 1) > 0
0
 

Author Closing Comment

by:Internet_Engineer
ID: 35168583
GREAT
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35168951
please check out this article, it has more performant options:
http://www.experts-exchange.com/A_1536.html
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query help 15 51
MSSQL join different row from other table 14 66
SQL Query Syntax error after > 11 39
Make an array show the subkey and put it in a query 2 22
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question