Complete Guide to SQL WHERE Clause Operators and Examples
Explore the various operators and examples used in SQL WHERE clauses, such as IN, CONTAINS, BETWEEN, LIKE, and more. Find out how to filter data effectively using these operators like a pro. Learn about the ESCAPE clause for searching actual percent or underscore characters and see practical examples to understand its usage better.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
WHERE clause operators Operator Example whereJobCategoryin('PT','NA','FA') IN where word ? 'LAM' CONTAINS or ? IS NULL or IS MISSING where Product_ID is missing where Salary between 70000 and 80000 BETWEEN AND SOUNDS LIKE (=*) where LastName =* 'SMITH' where Employee_Name like 'H%' where JobCategory like '__1' LIKE using % or _ Non-ANSI standard
The contains operator
Select only those rows where the employees first names begin with N proc proc sql quit quit; sql; select Employee_Name from orion.Employee_Addresses(obs=10 ; select Employee_Name, Employee_ID from orion.Employee_Addresses where Employee_Name contains ', N' ; 10)
The like operator _ (underscore) a single character % any number of characters
I would like a list of job codes that include SA followed by an underscore. proc proc sql select Employee_ID, Job_Code from Employee_Organization2 where Job_Code like 'SA_%' ; quit quit; sql;
ESCAPE Clause To search for actual percent or underscore characters in your text using the LIKE operator, you must use an ESCAPE clause. The ESCAPE clause in the LIKE condition enables you to designate a single character string literal, known as an escape character, to indicate how PROC SQL should interpret the LIKE wildcards (% and _) when SAS is searching within a character string.
ESCAPE Clause proc proc sql select Employee_ID, Job_Code from work.Employee_Organization2 where Job_Code like 'SA/_%' ESCAPE '/' ; quit quit; sql;
Job titles with levels proc proc sql create table work.Employee_Organization2(drop=code1-code3) as select *, scan(Job_Title,1 1,' ') as code1 length=1 1, scan(Job_Title,2 2,' ') as code2 length=1 1, scan(Job_title,3 3,' ') as code3 length=3 3, case when calculated code3='II' then calculated code1|| calculated code2|| /*for demonstrating escape*/ '_'||calculated code3 else calculated code1|| calculated code2|| calculated code3 end as Job_code from orion.Employee_Organization where Job_Title contains 'I';/*only "level" employees*/ select * from employee_organization2; quit quit; sql;
