Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

T-SQL Expand / Collapse
Author
Message
Posted Monday, January 10, 2011 3:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 8:03 AM
Points: 131, Visits: 203
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




Post #1045190
Posted Monday, January 10, 2011 7:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:54 AM
Points: 1,032, Visits: 1,295
@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
Post #1045313
Posted Monday, January 10, 2011 8:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 8:03 AM
Points: 131, Visits: 203
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



Post #1045329
Posted Tuesday, January 11, 2011 7:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 18, 2014 6:11 PM
Points: 110, 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)
Post #1046226
Posted Friday, January 14, 2011 3:55 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:43 PM
Points: 697, Visits: 155
I think this part is not correct
INSERT INTO @student
VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99)

it will throw error.
Post #1047808
Posted Sunday, January 16, 2011 9:37 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:54 AM
Points: 1,032, Visits: 1,295
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
Post #1048537
Posted Tuesday, January 18, 2011 3:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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
Post #1049176
Posted Wednesday, January 19, 2011 3:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 5,925, Visits: 8,173
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
Post #1049867
Posted Wednesday, January 19, 2011 5:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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
Post #1049943
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse