Oracle LIKE clause

In this tutorial, you will learn how to use the Oracle LIKE operator to test whether a value in a column matches a specified pattern.

The Oracle Like operator describes

Sometimes, you want to query the data according to the specified pattern. For example, you might want to find contacts whose last name St begins with the first or last name with er the end. In this case, you can use the Oracle LIKE operator.

LIKEThe syntax of the Oracle operator is as follows:

expresion [NOT] LIKE pattern [ ESCAPE escape_characters ]

In the syntax above,

• expression-This is a column name or an expression that you want to test against for that pattern ( pattern ).
• Pattern-The pattern is a string that is searched in an expression. This pattern contains the following wildcard characters:
  • % (Percent semicolon) matches any string of 0 or more characters.
  • _ (underline) matches any single character.
• escape_character - escape_character is the character that appears in front of wildcards and is used to specify that wildcards should not be interpreted as wildcards but as regular characters.

escape_character(if specified) must be a character, and there is no default value.

If an expression matches the pattern, the LIKE operator returns true . Otherwise, it returns false .

NOTOperator (if specified) LIKE The result of the negation operator.

Oracle like example

Here are some examples of using LIKE the Oracle operator to see how it works.

This will be demonstrated using the tables in the sample database contacts :

1. Examples of% wildcard characters

The following example uses ％ a wildcard to find the St phone number of a contact whose last name begins with:

SELECT    first_name,    last_name,    phoneFROM    contactsWHERE    last_name LIKE ‘St%‘ORDER BY    last_name;

Execute the above query statement to get the following results-

In this example, this pattern is used:

‘St%‘

LIKEThe operator matches any “St” string that begins with any number of characters, such as Stokes , Stein or, and Steele so on.

To find the phone number of a contact whose last name ends with a string “er” , use the following statement:

SELECT    first_name,    last_name,    phoneFROM    contactsWHERE    last_name LIKE ‘%er‘ORDER BY    last_name;

Execute the above query statement to get the following results-

Matching pattern-

%er

Matches any “er” string that ends with a string.

To perform a case-insensitive match, you can use the LOWER() or UPPER() function as follows:

UPPER( last_name ) LIKE ‘ST%‘LOWER(last_name LIKE ‘st%‘

For example, the following statement looks up CH e-mail for a contact whose name begins with:

SELECT    first_name,    last_name,    emailFROM    contactsWHERE    UPPER( first_name ) LIKE ‘CH%‘ORDER BY    first_name;

Execute the above query statement to get the following results-

The following example uses NOT LIKE operators to find contacts whose phone numbers do not “+1” begin with:

SELECT  first_name, last_name, phoneFROM  contactsWHERE  phone NOT LIKE ‘+1%‘ORDER BY  first_name;

Execute the above query statement to get the following results-

_ Examples of wildcard characters

The following example finds the “Je_i” phone number and e-mail for a contact whose name has the following pattern:

SELECT    first_name,    last_name,    email,    phoneFROM    contactsWHERE    first_name LIKE ‘Je_i‘ORDER BY     first_name;

Execute the above query statement to get the following results-

The pattern ‘Je_i‘ matches any ‘Je‘ string that begins with a character, and then the last is ‘i‘ , for example, Jeri or Jeni , but not Jenni .

3. Example of a mixed wildcard character

You can mix wildcards in a pattern. For example, the following statement looks for a name that begins with a J two-character followed by a contact of any number of characters. In other words, it will match Je 4 any last name () with a beginning and at least one character first_name :

SELECT    first_name,    last_name,    email,    phoneFROM    contactsWHERE    first_name LIKE ‘Je_%‘;

Execute the above query statement to get the following results-

4. Examples of escape clauses

ESCAPEclause allows you to find a string that contains one or more wildcard characters.

For example, a table might contain data with percent characters, such as discount values, depreciation rates. To search 25％ for a string, you can use the ESCAPE clause as follows:

LIKE ‘%25!%%‘ ESCAPE ‘!‘

If you do not use a ESCAPE clause, Oracle returns 25 any line that is a string.

The following statement creates a discount ( discounts ) table and inserts some sample data for testing:

CREATE TABLE discounts  (    product_id NUMBER,     discount_message VARCHAR2( 255 ) NOT NULL,    PRIMARY KEY( product_id )  );-- 插入3条数据INSERT INTO discounts(product_id, discount_message)VALUES(1, ‘Buy 1 and Get 25% OFF on 2nd ‘);INSERT INTO discounts(product_id, discount_message)VALUES(2, ‘Buy 2 and Get 50% OFF on 3rd ‘);INSERT INTO discounts(product_id, discount_message)VALUES(3, ‘Buy 3 Get 1 free‘);

If you are unfamiliar with the statements used in this script, you can learn them in the tutorials that follow. The following statement retrieves 25％ the product for the discount:

SELECT   product_id, discount_messageFROM   discountsWHERE   discount_message LIKE ‘%25!%%‘ ESCAPE ‘!‘;

Execute the above query statement to get the following results-

Oracle LIKE clause