Collections:
IN - Testing Value in a Value List in SQL Server
What To Test Value Lists with the IN Operator in SQL Server Transact-SQL?
✍: FYIcenter.com
Sometimes you want to test a given value against a list of values. You can do this in a loop with the regular "equal" operator. But you can also use the special comparison operator IN to get it done with the following syntaxes:
1. In-the-list test test_value IN (value_1, value_2, ..., value_n) - Returns the same results as the following expression test_value = value_1 OR test_value = value_2 ... OR test_value = value_n 2. Not-in-the-list test test_value NOT IN (value_1, value_2, ..., value_n) - Returns the same results as the following expression test_value <> value_1 AND test_value <> value_2 ... AND test_value <> value_n
Here are two examples of using the IN operator:
DECLARE @my_number INT; SET @my_number = 767; SELECT CASE WHEN @my_number IN (525, 272, 532, 767, 150, 637) THEN 'You are a winner.' ELSE 'You are not a winner.' END; GO You are a winner. DECLARE @my_number INT; SET @my_number = 676; SELECT CASE WHEN @my_number NOT IN (525, 272, 532, 767, 150, 637) THEN 'You are not a winner.' ELSE 'You are a winner.' END; GO You are not a winner.
⇒ LIKE - Matching a Pattern in a Character String in SQL Server
⇐ BETWEEN - Testing Value in a Range in SQL Server
⇑ Boolean Values and Logical Operations in SQL Server Transact-SQL
2017-01-21, 1348🔥, 0💬
Popular Posts:
How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...
What Is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is ...
Where to find answers to frequently asked questions on Downloading and Installing SQL Server 2005 Ex...
What Are Out-of-Range Errors with DATETIME values in SQL Server Transact-SQL? When you enter DATETIM...
How To List All Stored Procedures in the Current Database in SQL Server Transact-SQL? If you want to...