Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: LIKE Condition Jump to:  
Category: >> Oracle PL/SQL >> LIKE Condition Bookmark and Share

<< lastnext >>

Snippet Name: LIKE Condition

Description: The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.

Also see:
» BETWEEN Condition
» Combining the AND and OR Conditions
» OR Condition
» AND Condition
» WHERE Clause - Joins
» WHERE Clause - Conditions
» Having Clause

Comment: (none)

Language:
Highlight Mode: PLSQL
Last Modified: March 07th, 2009

The patterns that you can choose FROM are:
 
% allows you TO match ANY string OF ANY LENGTH (including zero LENGTH)
 
_ allows you TO match ON a single character
 
 
 
Examples using % wildcard
 
The FIRST example that we'll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with 'Hew'.
 
SELECT * FROM suppliers
WHERE supplier_name like 'Hew%';
 
 
 
You can also using the wildcard multiple times within the same string. For example,
 
SELECT * FROM suppliers
WHERE supplier_name like '%bob%';
 
In this example, we are looking for all suppliers whose name contains the characters 'bob'.
 
 
 
You could also use the LIKE condition to find suppliers whose name does not start with 'T'. For example,
 
SELECT * FROM suppliers
WHERE supplier_name not like 'T%';
 
By placing the not keyword in front of the LIKE condition, you are able to retrieve all suppliers whose name does not start with 'T'.
 
 
 
Examples using _ wildcard
 
Next, let's explain how the _ wildcard works. Remember that the _ IS looking FOR only one character.
 
FOR example,
 
SELECT * FROM suppliers
WHERE supplier_name LIKE 'Sm_th';
 
This SQL statement would RETURN ALL suppliers whose name IS 5 characters LONG, WHERE the FIRST two characters IS 'Sm' AND the LAST two characters IS 'th'. FOR example, it could RETURN suppliers whose name IS 'Smith', 'Smyth', 'Smath', 'Smeth', etc.
 
 
 
Here IS another example,
 
SELECT * FROM suppliers
WHERE account_number LIKE '12317_';
 
You might find that you are looking FOR an account NUMBER, but you only have 5 OF the 6 digits. The example above, would retrieve potentially 10 records back (WHERE the missing VALUE could equal anything FROM 0 TO 9). FOR example, it could RETURN suppliers whose account numbers are:
 
123170
123171
123172
123173
123174
123175
123176
123177
123178
123179.
 
 
 
Examples using Escape Characters
 
Next, IN Oracle, let's say you wanted to search for a % or a _ character in a LIKE condition. You can do this using an Escape character.
 
Please note that you can define an escape character as a single character (length of 1) ONLY.
 
For example,
 
SELECT * FROM suppliers
WHERE supplier_name LIKE '!%' escape '!';
 
This SQL statement identifies the ! character as an escape character. This statement will return all suppliers whose name is %.
 
 
 
Here is another more complicated example:
 
SELECT * FROM suppliers
WHERE supplier_name LIKE 'H%!%' escape '!';
 
This example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as 'Hello%'.
 
 
 
You can also use the Escape character with the _ character. For example,
 
SELECT * FROM suppliers
WHERE supplier_name LIKE 'H%!_' escape '!';
 
This example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as 'Hello_'.
 
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 256 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?