Examples using _ wildcard
Next, let's explain how the _ wildcard works. Remember that the _ is looking for only one character.
For example,
SELECT * FROM supplier
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 supplier
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 supplier
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 supplier
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 supplier
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_'.
|