1z0-071 Oracle Database SQL – PATTERN COMPARISON – LIKE OPERATOR

1. LIKE OPERATOR – WITH WILDCARDS % AND _ (UNDERSCORE)

We can also fetch rows based on certain patterns. We need to use like operator for that. Like operators may use any of the following wildcard characters percentage, symbol and underscore. Symbol percentage can be replaced with zero or more characters of any characters, while pattern matching for example or percentage can represent oracle, organization, orange, arbitrary, etca. If you notice there is no limit on the number of characters, also it can represent zero characters as in the example R. However, an underscore represents only one character.

For example if there are rows with values ora and then they can be fetched by the pattern or underscore. Remember, one underscore will represent one character and it can be any character, but it cannot represent strings that have more number of characters than the underscore in the pattern condition. If there are rows with value orange then it won’t be a part of the result as it has more number of characters. It also cannot fetch the value R, as it cannot bring values with zero characters for the underscore. Unlike the wild card character percentage, these wild card characters can be used anywhere in the pattern.

They can be in the beginning as in percentage en which can match to Stephen. They can also be in the middle as in yes percentage n which can match Susan. Also the wild cards are not mandatory for a like operator. If the wild cards are absent then it will be treated as an equality operator. For example like Pat where there is no wild card will be treated as equal to Pat. And if a pattern just has the wild card, every row will be fetched except for null values. So a like percentage is equivalent of using is not null class.

2. LIKE OPERATOR – HANDS ON

Now let’s do some hands on with pattern comparison like operator. In this sequel, I am using Pat percentage as the pattern. Let me run it. It brings me rows with first name containing values Pat. After that, there is nothing which is equivalent to zero characters. The second row containing Patrick where R-I-C-K are matched by this percentage sign which means zero or any number of characters. Now let me run this sequel where the like operator uses PA percentage. Just to show you the difference between Pad percentage versus PA percentage.

This brings me a result that contains the string PA at the beginning for the column first name, it brought me three rows. I used PA percentage. Now let’s see the difference between PA percentage and PA underscore. As we saw earlier, it will bring only rows that contains three character string which begin with PA. And we have only one such row which is Pat. Now let’s verify if underscore supports zero characters. If it supports zero characters then Pat underscore should also bring this value, right? It doesn’t bring any value.

So we verified that Pat underscore is different from PA underscore. And underscore always expects a character in the row to match. Now I have used three underscore characters here. So that should bring me a row which begins with PA and then contains three more characters for a total of five characters for first name. Okay, we got pavam. Now we can use the wildcard character in the beginning of the pattern. Two percentage en will bring me every first name that ends with the letters en. Now this pattern yes percentage N will bring me every first name that begins with yes ends with N.

In between, it can have any number of any character. So here I’m not using any wild card character at all. So that will be treated as an equal comparison and it just brings the value Pat in the result. Let’s take a look at commission percentage column in this query. If you notice, it has lots of null values. Okay? And let’s see what happens if we just use a wildcard character in the pattern comparison. As we saw earlier, this is equivalent to is not null meaning that it should bring every row where the commission percentage is not null. So it brought every except for null values.

3. PATTERN COMPARISON – ESCAPE CHARACTER

Earlier, we saw that the like operator uses percentage and underscore as its wild card characters. Now, a question may arise. What if the actual data contains wild card characters? How can we retrieve data when the data itself has some wild card characters? For example, let us look at the data from Jobs table here. If we see the data from Job ID column, you will notice that it has underscores as part of the real data such as ad underscore press ad underscore VP like that. Now, I would like to select rows whose job ID is MK underscore followed by anything. And I do not want this underscore to be treated as a wild card, but instead just as a literal underscore. How do I achieve that? I can achieve that by using escape characters.

Look at this sequel. I’m using this pattern MK I’m going to use an escape character. In this case, I’m going to use hash followed by the underscore which I wanted to be treated as a literal. And then I’m going to use a wild card character percentage. Now I need to mention which is my escape character. Now I’m able to bring the rows whose job ID is MK underscore followed by anything. Okay, so in this SQL escape, hash tells that hash is the escape character. Then hash underscore tells to treat this underscore as just a literal and not a wildcard character. You can use any character as your escape character as long as you mention it here in the escape command.