Operators
Defines server operators and its uses
Following is a list of operators used in where clauses and descriptions
| Operator | Description | Example |
| = | All data that Equals value | SELECT * from book WHERE authorid=119 |
| <> | All data that does not equal value | SELECT * from category WHERE categoryid != 4 |
| > | All data that is Greater than the value | SELECT * from book WHERE pubdate > '2000' |
| < | All data that is less than the value | SELECT * from custorder WHERE orderdate < '1-Jan-2006' |
| >= | All Data that is greater than or Equal to the value | SELECT * from book WHERE pubdate >= '2000' |
| <= | All data that is Less than or equal to the value | SELECT * from custorder WHERE orderdate <= '1-Jan-2006' |
| AND, OR | Used to Separate values being compared | SELECT * from book WHERE subjectid = 5 OR subjectid = 10 |
| BETWEEN | Between two values including those two values | SELECT * FROM book WHERE pubdate Between '1980' And '1990' |
| LIKE |
Used to search for a string of alphabets, numbers, or other characters |
SELECT * from author WHERE lastname LIKE 'Shakespeare' OR firstname LIKE 'William' |
| IN |
Used to search for a set of values |
SELECT * from book WHERE subjectid IN (1,5,10,15) |
The WHERE clause is one of the most useful features of the language. Try out all the examples in the above table in a T-SQL query window and see the results. The equals, less than/greater than signs, and between keywords are used mainly for numbers and dates while the LIKE clause is used for comparing words and other character strings. We can use the '=' operator for comparing short strings but using LIKE makes more sense.
Most of the time, we only know part of the string we are searching for. Can SQL Server get us information about such strings? The '%' keyword is used as a wild card in matching strings in SQL. How would someone look for all books that begin with an 'A'?
Select * from book where title LIKE 'A%' Select * from book where title like 'Harry Potter%'
Another wildcard is the '_' underscore. While the % sign matches any number of characters, the '_' matches just one. The following command would return all books that start with two letter words with a leading 'A'. We match the 'A' followed by any character followed by a space and any sequence.
Select * from book where title LIKE 'A_ %'
To specify a range of valid characters, enclose the range in a pair of square brackets. For example, [a-z] matches any lowercase alphabet, [0-9] matches any digit, and [AaBbCc1-3] matches a-c upper or lower case and 1, 2, or 3. A caret '^' right after the initial square bracket negates the character set - anything but the characters within the brackets containing a caret are matched. That is [^a-c] matches anything but lowercase alphabets a through c. To find square brackets, the dash, the percent symbol etc literally, precede the symbol with a backslash (^ or \%).
