SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL


T-SQL

Author
Message
sfellner
sfellner
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 214
How about adding delimiters? Avoiding ardy and ocky.
DECLARE @inputValue NVARCHAR(20)
DECLARE @student TABLE
(Id INT PRIMARY KEY IDENTITY(1,1),
StudentName NVARCHAR(50),
StudentResult INT)

INSERT INTO @student
VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99), ('ardy', 97), ('ocky', 96)

--Select 1
SET @inputValue = 'Hardy'
SELECT * FROM @student WHERE StudentName IN (@inputValue)

--Select 2
SET @inputValue = 'Hardy,Rocky'
SELECT * FROM @student WHERE CHARINDEX(','+StudentName+',', ','+@inputValue+',') > 0





Hardy21
Hardy21
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1624 Visits: 1399
@sfellner : If you modify Select 2 as follows:

--Select 2
SET @inputValue = 'Hardy, Rocky, ardy'
SELECT * FROM @student WHERE CHARINDEX(','+StudentName+',', ','+@inputValue+',') > 0


It will only return 'Hardy'.

Thanks
sfellner
sfellner
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 214
it is because in the sample the delimiter became now comma and space.
both possibilities will work:


SET @inputValue = 'Hardy, Rocky, ardy'
SELECT * FROM @student WHERE CHARINDEX(', '+StudentName+',', ', '+@inputValue+',') > 0

SET @inputValue = 'Hardy,Rocky,ardy'
SELECT * FROM @student WHERE CHARINDEX(','+StudentName+',', ','+@inputValue+',') > 0



HTH
Stefan



dr.kusnadi
dr.kusnadi
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 472
the question should include SQL versions. the following does not work prior to 2008.

INSERT INTO @student
VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99)
amit_adarsh
amit_adarsh
Right there with Babe
Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)

Group: General Forum Members
Points: 732 Visits: 169
I think this part is not correct
INSERT INTO @student
VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99)

it will throw error.
Hardy21
Hardy21
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1624 Visits: 1399
amit_adarsh (1/14/2011)
I think this part is not correct
INSERT INTO @student
VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99)

it will throw error.


This is SQL Server 2008 syntax.
If you want to execute it in SQL 2005 then use the following:

INSERT INTO @student
VALUES ( 'Hardy', 100)
INSERT INTO @student
VALUES ('Rocky', 98)
INSERT INTO @student
VALUES ('Panky', 99)



Thanks
michael.kaufmann
michael.kaufmann
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1343 Visits: 1082
Thanks for the question.

It may sound stupid to the more experienced users, but I don't understand why the following code is not working (using SQL Server 2005; pasted just my amended query--you'll need the original one, too):


--Select 3
SET @inputValue = '''Hardy''' + ', ' + '''Rocky'''
select @inputValue -- added to view the output of the variable
SELECT * FROM @student WHERE StudentName IN (@inputValue) -- not working


Because if you write out the query without the variable, it will return the expected 2 records.


SELECT * FROM @student WHERE StudentName IN ('Hardy', 'Rocky') -- working OK


Any hints / explanations are welcome.

Thanks in advance,
Michael
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11274 Visits: 12005
michael.kaufmann (1/18/2011)
I don't understand why the following code is not working (using SQL Server 2005; pasted just my amended query--you'll need the original one, too):


--Select 3
SET @inputValue = '''Hardy''' + ', ' + '''Rocky'''
select @inputValue -- added to view the output of the variable
SELECT * FROM @student WHERE StudentName IN (@inputValue) -- not working


It is working, just not as you expect it. If you look at the code above, you'll see that the IN list consists of exactly one member - the variable @inputValue. SQL Server will not care or interpret the content of that variable, but simply search the @student table for rows with a StudentName that matches that value - so you get all rows returned for students whose name is equal to 'Hardy', 'Rocky' (and I hope for their sake that no student has been given that name!)

To get SQL Server to find both Hardy and Rocky, you need to supply two arguments, seperated by a comma. The comma has to be in the IN list, not in the contents of the variables.

--Select 3
SET @inputValue1 = 'Hardy';
SET @inputValue2 = 'Rocky';
--select @inputValue -- added to view the output of the variable
SELECT * FROM @student WHERE StudentName IN (@inputValue1, @inputValue2);



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
michael.kaufmann
michael.kaufmann
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1343 Visits: 1082
Hello Hugo,

as it's obvious, I haven't had too many chances using variables (except for variables carrying exactly one distinct value)--hence I was under the impression the contents of the variable is used.
Thank you very much for your kind and precise explanation.

Regards,
Michael
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search