Search Condition ( Interbase Tutorial # 9 )

NOT

You can use NOT if you want to retrieve data which value is not equal to the condition. The syntax is :

    SELECT column FROM table
    WHERE NOT column = value

The other way to get the same data is :

    SELECT column FROM table
    WHERE column != value

Example 1 : we want to retrieve all data except data which age is above or equal to 30.

    select * from employee where not age<30

the statement above will have the same output with this statement below :

    select * from employee where age>=30

Example 2 : we want to retrieve data except the data which name is Archimedes.

    select * from employee where name!=’Archimedes’

LIKE

LIKE is used for retrieving data which has a particular matches with the pattern condition you give. The syntax is :

    SELECT column FROM table
    WHERE column LIKE ‘%pattern’

Example 3 : we want to retrieve data which name is ended with a particular pattern ‘ton’ 

    select * from employee where name like ‘%ton’

Example 4 : we want to retrieve data which name has a particular pattern ‘chi’ in the middle.

    select * from employee where name like ‘%chi%’
 
Example 5 : we want to retrieve data which name is started with a particular pattern ‘Ge’ 

    select * from employee where name like ‘Ge%’

STARTING WITH

You can use STARTING WITH if you want to retrieve data which column’s value started with a pattern. The syntax is :

    SELECT column FROM table
    WHERE column STARTING WITH pattern

Example 6 : we want to retrieve data which name is started with ‘Bla’

    select * from employee where name STARTING WITH ‘Bla’

it will have same output with the statement below :

    select * from employee where name like ‘Bla%’

CONTAINING

You can use CONTAINING to retrieve data which contain a value. The syntax is :

    SELECT column FROM table
    WHERE column CONTAINING value

Example 7 : we want to retrieve data which name contains ‘s’ letter

    select * from employee where name containing ‘s’


BETWEEN and IN

BETWEEN is for retrieving data within a range value. The syntax is :

    SELECT column FROM table
    WHERE column BETWEEN value1 AND value2

The statement below will get the data which name is started with a letter between C and J.

    select * from employee where name between ‘C’ and ‘J’

IN is for retrieving data with several values to be compared. The syntax is :

    SELECT column FROM table
    WHERE column IN (value1, value2, value3…….)

The statement below will get the data which age is 25, 27 or 32

    select * from employee where age in (‘25’,’27’,’32’)

Go to Previous Tutorial or Next Tutorial