SQL - Predicates
Predicates follow the where clause. Predicates allow the searching through database records to recover specific strings and ranges or characters. Rows will be returned if they match the predicate condition.
SQL - Like; Not Like
Like is a means of quering data in your database by keyword or keyletter means. After a where clause, use LIKE to match a character or a string. SQL retrieves all rows containing all or part of the string placed within the parameters. Generally, a percent sign (%) is used to define the start and ending or your string. Escape characters can be used for special situations.
SQL Code:
SELECT * FROM employees WHERE Lastname LIKE '%H%';
SQL Table:
| ID | Lastname | Firstname | Title |
| 10 | Harris | Joel | crew |
| 9 | Hicks | Freddy | crew |
| 2 | Hively | Jessica | crew |
Our example will retrieve any and all rows with a capital letter H in the lastname field. Case sensitivity is important in this situation.
On a contrary note, use the Not Like predicate to find all rows that do not match the string.
SQL Code:
SELECT * FROM employees WHERE Lastname NOT LIKE '%H%';
SQL Table:
| ID | Lastname | Firstname | Title |
| 1 | Johnson | David | crew |
| 11 | Davis | Julie | manager |
SQL - Predicates Escaping Characters
Say you want to find a percent character(%) in your database, or all rows associated with one. SQL Server and Oracle, require an additional statement over MySQL. With Oracle and SQL Server, you must specify the character used to escape. Here's code example.
SQL Code:
Oracle/SQL Server
SELECT * FROM employees WHERE Lastname LIKE '%\%%' ESCAPE '\';
MySQL
SELECT * FROM employees WHERE Lastname LIKE '%\%%'
MySQL has a built in default escape character the backslash (\). MySQL also supports the escape predicate allowing you to change your escape character exactly as it is done in the other programs.
SQL - Between
Between is a predicate to call a range of numeric values such as 1-20 or the like. Its syntax follows the same as above.
SQL Code:
SELECT * FROM employees WHERE id BETWEEN 1 AND 4;
SQL Table:
| ID | Lastname | Firstname | Title |
| 1 | Johnson | David | crew |
| 2 | Hively | Jessica | crew |
Between is essentially replacing your range operators. Always start your ranges with the lowest number first.
SQL - Limit
The limit predicate allows you to limit the number of rows selected.
SQL Code:
SELECT * FROM employees LIMIT 2;
SQL Table:
| ID | Lastname | Firstname | Title |
| 1 | Johnson | David | crew |
| 2 | Hively | Jessica | crew |
| 
