Hi, in this lesson, we understand how to use regular expressions in Oracle starting from Oracle 10 g we got support for regular expressions, we have four different functions dealing with regular expressions and one more was added subsequently, which is regular expression count. Regular Expression like is an enhanced version of our like operator. That means, it supports regular expression. And then regular expression in string substring and replace are similar to the string functions we have seen before. But these functions support regular expressions. But what is a regular expression?

Regular expression is a string that can have meta characters. And these meta characters specify what are we exactly looking for And if you see a few examples here, the first one says we are looking for any character in the given list. The list is consisting of ABC, so any character in that list will be a match to it. The next example shows not ABC. This is where I'm saying, I'm looking for anything other than ABC. Because the carrot symbol in square brackets is treated as not.

This is where we talk about a range of characters. And this is where we say the string should start with what is given after that. So in this case, the string should start with the digit and here the string should contain 10 digits. So this is where we are saying it should start with that it should end with that and in between, we need to have 10 days We can also specify minimum and maximum occurrences. So two comma four means minimum to maximum four. And another powerful concept is grouping.

We are grouping our expressions. This is group one, this is group two. We'll see some examples using regular expression related functions. The first function is regular expression like if the string is matching the pattern it returns true. We can also give match options. For example, if you gave I it means you want to ignore the case while you're comparing strings.

Let us say the first example. This is saying we are looking for employers where the first name is starting with STD ending with n. But in between, we can have either we are PGH So the pipe symbol says either this or that. So Stephen r Steven will be a match to this. This is how we form the regular expression. And you are using regular expression like to check whether the first name is a match to it. The second one is looking for street address where we have a desert at the beginning.

So, if the street address starts with a desert, it is a match. Let's go try them. So this is my first example when I run this, it is giving me all the names where we have st he in the beginning end at the end, in between you might have we are pch. The second example is looking for three desserts at the beginning. I just improvise date and I'm saying I'm looking for not one but two. DCs, there you are, you get all the street addresses where we have at least three visits?

Well, I can increase it to five or whatever I want. So if I say five, I will get less number of matches. In fact, I'm getting only one. Well, that's about it. The next regular expression function is in string, unlike like, it is not returning Boolean, it is returning the position of the match. So this is where I'm looking for the first alphabet in the street address.

And this is one way to say we are looking for an alphabet you can replace this with something like small a to small Jade capital A to capital jet, if you like, or you can give alpha in this way. So we are looking for the first alpha beta In string returns the position. The second example is looking for at least two deserts. So wherever we have two deserts, it's going to return the position of that particular location. These are examples for regular expression in string and substring is extracting whatever is matching the regular expression. So here we are looking for in the street address one or more digits.

So wherever in the street address you find one or more digits, you extract that, but this is slightly more complex. We are looking for a space followed by one or more because this plus means one or more. But what we are not looking for is desert ends. space. So start with the space, take all the characters other than deserts and spaces. And that will be the match.

So we are using not g zero to nine and space. Let's check how it works. So this is my first example, where I'm looking for regular expression in string. And using this to find out the position of first alphabet, and when you run you get something like this. So in this the first alphabet is we that is in sixth position. And similarly regular expression substring.

And here, let's take this first, I'm looking for all the desserts. So whatever desserts we have, we get that of course, if you have multiple numbers, it will give you only the first number. It's not going to take the remaining and the next day What is a regular expression replace, this can be used to replace a string with another string, but the first one is a regular expression. So, wherever you have a dot, I want that to be replaced it with hyphens. But this backslash before.is needed because.is itself a meta character. But in this case, I want to treat a.as a literal not as a special character.

And I'm using place just to indicate in case you have more than one dot, even all those dots are to be replaced with a hyphen. So if you go and run this, we replace all the occurrences of dots in the string with hyphen. And this is another example just to demonstrate how we can replace one or more diseases. with nine, so wherever you have a desert, replace that with the nine. So 515 all of them are replaced with the nine. And this is pretty powerful.

We are using grouping concept. So let's understand this. I'm using two groups here. The first group is looking for one or more desserts at the beginning of the string. Then it is looking for a dot, then it is looking for zero or more characters, and this.is treated as the literal dot, but this.is treated as a special character. It means it's a match to any character.

And I'm looking for two sets. The first set is one or more desserts. The second set is any set of characters. And this is represented as a backslash one first group This is represented as backslash two. Now I'm saying replace the original string with the new string, where you put the first group first, followed by hyphen, followed by second group in parenthesis. Well, let's run this to see how exactly it works.

Now there is our first number, and it is picking up these three desserts that becomes the first group, then it is putting hyphen, and anything after this dot, the rest of that is going to be second group, and that is placed in parentheses. This is our second group. So we can use grouping concept and select multiple values using multiple patterns like this. And this is a very useful function. It's like replays but it supports regular expression. So We can precisely select what we want.

The last function is regular expression count, which is just counting how many times the given pattern is found. And this is going to tell you how many times you have four or more alphabets in the street address. So in this case, it is finding three occurrences of string with four or more characters. Whereas in this way found only one color is the only match to it. So those are the options, regular expression substring to extract the string. Regular Expression replace is replacing one string with another count is counting.

So in this lesson, we understood what is regular expression and how to use those five functions related to regular expressions. That's it for now. I'll catch you in In the next lesson