T-SQL

Question

Post reply

T-SQL

Viewing 9 posts - 31 through 38 (of 38 total)

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

sfellner SSC Enthusiast Points: 141 More actions · Answer 1

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 SSCrazy Eights Points: 9708 More actions · Answer 2

@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 SSC Enthusiast Points: 141 More actions · Answer 3

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 Say Hey Kid Points: 696 More actions · Answer 4

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 Ten Centuries Points: 1273 More actions · Answer 5

I think this part is not correct

INSERT INTO @student

VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99)

it will throw error.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 6

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 SSCrazy Points: 2816 More actions · Answer 7

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 SSC Guru Points: 64780 More actions · Answer 8

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 [font="Courier New"]'Hardy', 'Rocky'[/font] (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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

michael.kaufmann SSCrazy Points: 2816 More actions · Answer 9

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