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.
Conceptual
figure
Syntax
The syntax for SQL LIKE Condition is as follows:
Expression WHERE <column_name> [NOT] LIKE 'pattern' [ESCAPE 'escape_characters ]
Description
Pattern is made up with some wild cars characters like:
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).
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).
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)
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)
Examples
The below example illustrates the procedural steps for like clause.
[c]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)
[/c]
Key Points
- SQL LIKE Condition - Used for matching the wildcard character.