You are here

Like Operator in SQL

like operator in sql

Like Operator in SQL

In this post we will discuss Like Operator in SQL with example and it’s used in Query.

  • The LIKE allows comparison of one string value with another string value. This is achieved by using wildcard character.
  • Using LIKE operator we can filter character string data based on pattern matching.
  • We can use LIKE operator in where clause of select, update, Delete statement.
  • Use of wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators for string filtering.

syntax:

SELECT
col1, col2
FROM
table
WHERE
column LIKE pattern;

  • The wildcards used for pattern matching are:
Wildcard Meaning Example
%(percent) Any string including an empty one ‘Ri%’ string starting with Ri.
_(underscore) A single character ‘_A’% string where second character is A.
[<character list>] A single character from a list ‘[RP]%’ string where first character is R or P.
[^<character list or range>] A single character that is not in the list or range. ‘[^0-9]%’ string where first character is not a digit.

Example:

Table: tblemp

eid Ename city joindate
1 Riya V.V.Nagar 2016-01-01
2 Priya Anand 2017-08-12
3 Diya Nadiad 2016-12-31
4 Jiya Bakrol 2017-09-28

Retrieve the employee names that have a city begin with ‘Ba’

select ename from tblemp where eid IN(1,4) AND city LIKE ‘Ba%’;

output:

Ename
Jiya

Retrieve the name of employee that not start with ‘P’

select ename from tblemp where ename NOT LIKE ‘P%’;

output:

Ename
Riya
Diya
Jiya

Retrieve the employee that city second letter is ‘n’

select * from tblemp where city LIKE N’_a%’;

output:

Eid ename City joindate
3 Diya Nadiad 2016-12-31
4 Jiya Bakrol 2017-09-28

Retrieve the employee table record where ename start with either R or P.

select * from tblemp where ename like N'[RP]%’;

output:

Eid ename City joindate
1 Riya V.V.Nagar 2016-01-01
2 Priya Anand 2017-08-12

Retrieve the employee table record where city second letter is not ‘n’.

select * from tblemp where city like N’_[^n]%’;

output:

Eid ename City joindate
1 Riya V.V.Nagar 2016-01-01
3 Diya Nadiad 2016-12-31
4 Jiya Bakrol 2017-09-28

Download code [media-downloader media_id=”260″ texts=”Download Code”]

Keep Visiting TechwithR.com for more upcoming top technical articles and also see my second blog Mag4Info.

Detail course about DBA visit TechNet Consultancy .

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top