The SQL-like operator is used in WHERE the clause that compares a column value to similar values using wildcard operations. LIKE operator is used to filtering records and pattern matching for columns that are of VARCHAR data type. It can be used with SELECT, INSERT, UPDATE or DELETE statement in SQL.

We can also use the NOT operator with like when we want to negate the results of LIKE operator. It excludes rows from the result set that contains a specified pattern.

We can use two wildcards with LIKE Operator.

Syntax of SQL Like Operator

expression [NOT] LIKE pattern [ ESCAPE 'escape_character' ];

The below table gives some examples of SQL Like operators.

Examples used in this tutorial are based on this Oracle SQL file that you can find on my GitHub page.

Using percent(%) sign Wildcard

LIKE Operator

Let’s see an example where we will use a percentage sign(%) wild card to select the data.

SELECT * FROM OT.CUSTOMERS_DATA WHERE NAME LIKE 'As%';

As we can see, two records in the table start with the letter As. Now we will select the data from the customer’s data table whose name ends with y using a percentage sign.

SELECT * FROM OT.CUSTOMERS_DATA WHERE NAME LIKE '%y';

Let’s see how we use the % wildcard multiple times within the same string.

SELECT * FROM OT.CUSTOMERS_DATA WHERE NAME LIKE '%y%';

Here, we are trying to find the names in the customer data table which have y in between.

NOT LIKE Operator

Let’s look at some examples using NOT LIKE operator. It is going to return the results from CUSTOMERS_DATA table whose name column does not have y at the end.

SELECT * FROM OT.CUSTOMERS_DATA WHERE NAME NOT LIKE '%y';

Let’s look at another example where we will select the data from the customer’s data table whose names do not have 'y in between.

SELECT * FROM OT.CUSTOMERS_DATA WHERE NAME NOT LIKE '%y%';

As we see from the above query, we are getting results from customers’ data tables whose name columns do not have y in between.

Using Underscore(_) wildcard

Let’s see how the underscore _ wildcard works in the Oracle LIKE condition. In this example, we are only trying to match the last character in the column name.

SELECT * FROM OT.CUSTOMERS_DATA WHERE NAME LIKE 'Assuran_';

When we use the underscore wildcard, we need to make sure that the length of the column to be compared is of the same length. Otherwise, Oracle won’t give us any results from the query.

Now, let’s take a look at another example where we will use an underscore wildcard to filter columns at the start and end of the string.

SELECT * FROM OT.CUSTOMERS_DATA WHERE NAME LIKE '_ssuran_';

Conclusion

In this blog post, we looked into how SQL LIKE operation can be used alongside WHERE operators in the Oracle database. We also looked into the percent(%) and underscore(_) wildcard.