Where Clause, Like Condition


WHERE Clause

To conditionally select data from a table, a WHERE clause can be added to the SELECT statement. Run the query SELECT * FROM People WHERE ID = 'tjones':

mysql> select * from people where ID = 'tjones';
+--------+-----------+------------+----+------------+ 
| ID     | NAME_LAST | NAME_FIRST | MI | POSITION   | 
+--------+-----------+------------+----+------------+ 
| tjones | Jones     | Tyler      | R  | Technician | 
+--------+-----------+------------+----+------------+ 
1 row in set (0.00 sec) mysql>

Note the single quotes around the WHERE Field value. If the WHERE Field value is a number, do not provide quotes.

CORRECT:
  • SELECT  fieldname1  FROM   tablename  WHERE   fieldname2  =  ' string '
  • SELECT  fieldname1  FROM   tablename  WHERE   fieldname2  =  number


  • INCORRECT:
  • SELECT  fieldname1  FROM   tablename  WHERE   fieldname2  =  string
  • SELECT  fieldname1  FROM   tablename  WHERE   fieldname2  =  ' number '

  • With the WHERE Clause, these conditions can be used:

    Operator
    =
    !=  or  <>
    >
    <
    >=
    <=
    BETWEEN
    LIKE
    IS NOT NULL
    IS NULL
    REGEXP
    Condition
    Equal
    Not equal
    Greater than
    Less than
    Greater than or equal
    Less than or equal
    Between an inclusive range
    Explained below
    address is not null
    address is null
    regular expression

    LIKE Condition

    The LIKE Condition is used to specify a search for a pattern in a column. The syntax is thus: SELECT fieldname1 FROM tablename WHERE fieldname2 LIKE pattern.   A "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

    SELECT * FROM People WHERE NAME_LAST LIKE 'S%' to return records of people with a last name that starts with an 'S':

    mysql> select * from people where NAME_LAST like 'S%';
    +--------+-----------+------------+----+----------+ 
    | ID     | NAME_LAST | NAME_FIRST | MI | POSITION | 
    +--------+-----------+------------+----+----------+ 
    | bsmith | Smith     | Barbara    | N  | Clerk    | 
    | nsmith | Smith     | Nancy      | B  | Manager  | 
    +--------+-----------+------------+----+----------+ 
    
    2 rows in set (0.00 sec) mysql>

    SELECT * FROM People WHERE POSITION LIKE '%n' to return persons with a position that ends with a 'n'.

    mysql> select * from people where POSITION like '%n';
    +--------+-----------+------------+----+------------+ 
    | ID     | NAME_LAST | NAME_FIRST | MI | POSITION   | 
    +--------+-----------+------------+----+------------+ 
    | tjones | Jones     | Tyler      | R  | Technician | 
    +--------+-----------+------------+----+------------+ 
    
    1 row in set (0.00 sec) mysql>

    SELECT * FROM People WHERE ID LIKE '%smi%' to return persons with an ID that contains the pattern 'smi'.

    mysql> select * from people where ID like '%smi%';
    +--------+-----------+------------+----+----------+ 
    | ID     | NAME_LAST | NAME_FIRST | MI | POSITION | 
    +--------+-----------+------------+----+----------+ 
    | bsmith | Smith     | Barbara    | N  | Clerk    | 
    | nsmith | Smith     | Nancy      | B  | Manager  | 
    +--------+-----------+------------+----+----------+ 
    
    2 rows in set (0.01 sec) mysql>

    Select   < <  PREVIOUS   Table of Contents NEXT  > >   And/Or, Between

    Developed with HTML-Kit
    Sandersongs Web Tutorials
    Contact the Webmasterwith comments.
    ©2017, by Bill Sanders, all rights reserved.
    This domain had 3,991 different visits in the last 30 days.
    435,466 hits on this domain since 24 Nov 2006.
    http://www.sandersongs.com/PHPsqlCourse/SQL07.php
    This page was last modified on our server on 4 Jun 2008
    and last refreshed on our server at 12:31 pm, MST
    This file took 0.01356 seconds to process.