Combining Wildcard Characters

Combining Wildcard Characters in Oracle SQL

Topic Introduction: In the Previous tutorial, we learned to use the like operator. now we will discuss using Combining Wildcard Characters in Oracle SQL after the like operator. The % and _ symbols can be used in any combination with literal characters. and the ESCAPE identifier to search for the actual % and _ symbols.

Example: 

SELECT last_name

FROM employees

WHERE last_name LIKE '_o%' ;

The example in the slide displays the names of all employees whose last names have the letter 'o' as the second character.

Using details with example

1. Single Character: _ (underscore)

SELECT last_name, salary

FROM employees

WHERE salary LIKE '2_000';

2. Multiple Characters: % (percentage sign)

SELECT last_name, salary

FROM employees

WHERE salary LIKE '2%0';

3. Mixed Single And Multiple Characters: 

SELECT last_name, salary

FROM employees

WHERE last_name LIKE '_a%y';

4. Complex Statement: 

SELECT last_name, salary

FROM employees

WHERE last_name LIKE '_a%a%';

ESCAPE Identifier

If we need to have an exact match for the actual % and _ characters, use the ESCAPE identifier. This option specifies what the escape character is. If we want to search for strings that contain SA_, you can use the following SQL statement:

SELECT first_name, last_name, job_id

FROM employees 

WHERE job_id LIKE '%IT\_%' ESCAPE '\';

The ESCAPE identifier identifies the backslash (\) as the escape character. In the SQL statement, the

escape character precedes the underscore (_). This causes the Oracle server to interpret the underscore literally.