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, 3065🔥, 0💬
Popular Posts:
How To Count Rows with the COUNT(*) Function in SQL Server? If you want to count the number of rows,...
Where to find Oracle database server tutorials? Here is a collection of tutorials, tips and FAQs for...
How To Convert Numeric Expression Data Types using the CAST() Function in SQL Server Transact-SQL? I...
What Is Transport Network Substrate (TNS) in Oracle? TNS, Transport Network Substrate, is a foundati...
How To Assign Debug Privileges to a User in Oracle? In order to run SQL Developer in debug mode, the...