SQL - SPLessons

SQL LIKE Condition

Chapter 25

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQL LIKE Condition

SQL LIKE Condition

shape Description

In SQL for pattern matching wildcard character is used with SQL Like Condition . This can be used in WHERE condition with Insert, Delete, Select and Update statement. The operator Like Condition is utilized as a part of a WHERE clause to scan for a predetermined example in a segment.

shape Conceptual
figure

shape Syntax

The syntax for SQL LIKE Condition is as follows:
Expression WHERE <column_name> [NOT] LIKE ‘pattern’ [ESCAPE ‘escape_characters ]

Pattern

shape Description

Pattern is made up with some wild cars characters like:

Pattern (%)

 Allow to match any length of string. (for example student_id LIKE ‘PH%’ ; this will return all the student information who have student id starting with PH and anything after that).

Pattern(_)

Allow to match only single character. (for example student_name LIKE ‘ADM_N’; this will return all the student information who have name of five letters and it starts with ADM and any single character between them and ends with N).

Pattern([])

 Allow to match any character in this [] bracket. (for example student_name LIKE ‘ADM[IOE]N; this will return all the student information who have name of five letters and it starts with ADM and any single character in I, O or E between them and ends with N)

Pattern(^)

 Allow to match any character not in this [] bracket. (for example student_name LIKE ‘ADM[^IOE]N; this will return all the student information who have name of five letters and it starts with ADM and any single character except I, O or E between them and ends with N)

shape Examples

The below example illustrates the procedural steps for like clause.

sql> create table employee(emp_id number(4),ename varchar2(20),sal number);
Query OK, 0 rows affected (0.32 sec)

sql> create table employee(emp_id number(4),ename varchar2(20),sal number);
Query OK, 0 rows affected (0.63 sec)

sql> insert into employee values(1001,'jack',12000);
Query OK, 1 row affected (0.10 sec)

sql> insert into employee values(1002,'mack',13000);
Query OK, 1 row affected (0.39 sec)

sql> insert into employee values(1003,'james',14000);
Query OK, 1 row affected (0.39 sec)

sql> insert into employee values(1004,'kate',15000);
Query OK, 1 row affected (0.39 sec)

sql> select * from employee;
+--------+-------+-------+
| emp_id | ename | sal   |
+--------+-------+-------+
|   1001 | jack  | 12000 |
|   1002 | mack  | 13000 |
|   1003 | james | 14000 |
|   1004 | Kate  | 15000 |
+--------+-------+-------+
4 rows in set (0.00 sec)

sql> select * from employee where ename like '%k';
+--------+-------+-------+
| emp_id | ename | sal   |
+--------+-------+-------+
|   1004 | Kate  | 15000 |
+--------+-------+-------+
1 rows in set (0.00 sec)

sql> select * from employee where ename like 'k%';
+--------+-------+-------+
| emp_id | ename | sal   |
+--------+-------+-------+
|   1001 | jack  | 12000 |
|   1002 | mack  | 13000 |
+--------+-------+-------+
2 rows in set (0.00 sec)

Summary

shape Key Points

  • SQL LIKE Condition – Used for matching the wildcard character.