Like Operator in SQL Queries

Select Query with Like Operator

Like operator is used of pattern matching from the column data value. It use % and _ for values, % is used for
multiple values and _ is used for single value in different position in data as you like to search.

_ stands for one character
% stands for multiple character

ESCAPE clause is used if you have data defined in column relevant to this _ and % char then you need to defined escape char to find this _ and % data in column value,

Example
Suppose you want to search the mid name “PAL” you have to placed % at both end of PAL. Following query find all PAL name in employees weather it occurred in front, mid of any name, last.

Select * from employees where upper(employee_name) like '%PAL%';

Example 1:
Search for last name SHARMA in your employee table.

-- Return rows whose Last value should be SHARMA
select * from employee where employee_name like '%SHARMA';

Example 2:
Search for first character we donot know, it will return all rows with different first character and last name char should be SHARMA.

Select * from employee where employee_name like '_SHARMA';

Note: It will give you result like ASHARMA and KSHARMA. It will check only for first character.

Example 3:
USE of ESCAPE Clause, If you data having _ in the value of that column suppose email id column having _  as data

select * from employee where email_id like '%\_sharma%' escape '\';

Note: It will take _ as data due to escape clause used in it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.