Hi. In this lesson, we understand how to deal with string functions. string functions are used with strings, which are nothing but character and character type. Most of these functions are self explanatory. For example, length function returns the number of characters in the string. Lower and Upper are used to convert string to lowercase and uppercase respectively.

In it cap is converting the first letter of every word to uppercase, and the remaining characters to lowercase l pad are pad are used for padding. So they are used to make a string of a specific length. And to do that they padded on the left or right depending on whether you use l pad or l pad with either spaces Or with the characters given. So, fill string there is about what is the character we want to use to fill that extra area. Trimming is done using l trim, our trim and trim we have three different functions l trim is to trim on the left by default it trims all spaces, but you can specify what you want to trim. So, is the case with our trim a trims on the right side called the trailing spaces.

L trim is about leading spaces. But if you want to give a command to trim on both sides, left and right, leading and trailing, then we need to use stream. So those are the three functions we use to deal with the trimming. substring is to take a part of the string What if you want to take Only first three characters of a string are what if you want to take three characters from fifth position, so on. That's what we can do with substring. And substring also allows you to ignore the length.

So you can start taking characters from the position. And everything that is there from the given position will be taken. It's very useful, and we use it in many cases in string is to look for a substring in the mainstream. I want to know whether a substring is present in mainstream. So s one is the main string, s two is the substring. If you don't give anything else we look for first occurrence of s two in s one.

But if you say you're looking for a steel from a specific position, you can give the position here. You can also mention which are currents you interested in, then it'll give you the position of the occurrence that is mentioned. And if the substring is not found in the mainstream, it is going to return zero to indicate that the substring is not present in mainstream as ASCII is to convert the given string to ASCII code, so if I give a string like ABC, it'll give me ASCII code for first letter which is 65. And similarly, see, hey, char is returning the character for the given number. So CH, ar have something like 48 would mean you get dessert zero, because that's the ASCII code for zero. Translate a is converting every occurrence of character given in from to the corresponding character into it is to translate one character to another character.

It's character based translation. When you want to replace a string with another string, we need to use replace, which is a string based replacement. So to better understand them, let's look at some examples. And we use dual tables so that we don't have to worry about any other table. And as your table contains only one row, the output of all these expressions will come for once. So length of Oracle is going to be six because that's what we have.

But what if you have some spaces? Well, spaces are always counted as characters. So if you have any space leading, trailing, or in between, everything is counted. Upper is converting the given string to all uppercase. The same is done with the lower everything is converted to lowercase in it cap is going to convert the first letter of every word to uppercase, and here d will be uppercase. In case if you have something like b A in uppercase, they will be converted to lowercase.

So first letter of every word uppercase, the remaining lowercase l pad is padding the given string to eight characters. It's going to pad it to eight characters by filling the extra space on the left with a star. So I have six characters in that so it has to put two stars on the left and then it has to place a racket because it is L pad means left side padding. But what if I gave only three instead of eight? Then interestingly, it will truncate that on To three characters. So, be careful, if what you give is not large enough to accommodate the whole string, it will be truncated.

L trim is pretty interesting because it can take whatever characters you want to trim on the leftmost. So, in this case, I want to trim all occurrences of zeros and hyphens on the leftmost, so it will keep truncating this, this this and also high fun, because we mentioned that even that is gone. So, it will stop only when it encounters one because that's the first character that is not in the list of characters to be trimmed. So the result of that is going to be just one funny one. And trim as I mentioned, is to trim all stars that are present in the leftmost rightmost position. So all leading starts all trailing stars are gone, because that's what we mentioned.

So we want to trim this from this string, the result is going to be just a racket. And substring will pick up only first three characters, because I said start from one go up to three. Of course, the indexing starts with the one because that is the way it goes in Oracle and the database we start from fifth position. So here is the fifth position, and we take everything from that position. So the result is going to be base. The next one is in string, which is taking the position of the character so where is this a is present here and the result of that is going to be three but in If I give something like x which is not there, you get result as zero.

So if you're looking for something like x, the result will be zero. And this is where I'm looking for a but not from the beginning, but from third position side says start looking for a not from first position, but from third position. So, it will give you the occurrence of a after that so the result is for and this is also the same but we are mentioning things slightly differently. We said we want to start from first position but we are interested in second occurrence not the first occurrence and that is also eventually the same a so the result of both will be for now substring is going to take first five characters from G Up title, we are just using our tables in this example. So, I want to pick up only first five characters and if you don't have five characters whatever is available that will be taken and this is where it is actually taking second word onwards, okay this is what I wanted, I want to take everything from second word ignoring first word, because this is the position from where it should start taking characters.

So, I want to take characters from this position. So look for space plus one and from there, whatever you find, I want to take that so that is about this substring it takes second word on words. And this is where we are taking the first word. This is saying, start at first position and then go up to the Space is going to take everything up to space. But you have some interesting stuff here, what if the job title does not have a space? So please check that and what happens if the job title is not having space?

What will you get from this substring And finally, we use uppercase to get rid of the case differences. And upper of job title is saying convert everything to uppercase, then compare that with this. So we are looking for jobs where the job title contains manager, but we do not mind whether it is in uppercase or lowercase because we convert everything to uppercase to make it case insensitive. The such is now case insensitive. And then we talked about ASCII and character This is giving you the ASCII code for first letter, this gives you the character for the given ASCII code. So, this is a that is 65 and this is where I want to translate every occurrence of star with a dot every occurrence of star is to be replaced with the dot and every occurrence of iPhone is not replace it with anything, we are not giving anything we are giving only one character here.

So, what it does, wherever you have star that will be replaced with a dot. But wherever you have iPhone that will be deleted. So, the result of that is you have starts replace it with the dot but hyphens deleted. So, that's an interesting possibility of course, If you want hyphen to be replaced with underscore or something you can give that here. But in this case, I want to demonstrate how we can delete some characters from a string. And the last function is where every occurrence of leaven G is to be replaced with 18 C. So it's a string replacement, it's looking for a string called Levin, G, and eight is then replacing that with 18 C. So those are the string functions.

Now let's just try a few of them. So here is your SQL Developer, the one that we have been using to understand how to write SQL commands. So here if I say I want to get the length of job title, we are going to get the job title. Okay, there we are, and it's going to give you the length. Well, if your display The job title also then it becomes more clear. That's where you are.

But if you want to take only the first word of that, we have seen how to do that. So substring of job title. And then we want to take the first word, so one comma, and then we say I N str, again, we talk about job title. And we are looking for space minus one to be more precise. So wherever you have sprays, find out the position and go up to the previous character, because we don't want the space to be included. So this is nesting the functions, the value that is returned by this function is used in the expression and that is pass it to another function.

Well, this can be done, go ahead and run and you see something interesting as a totally If you have only one word, that word is not included. The reason for that is you will in string is going to return zero and zero minus one is going to be minus one. That means you're saying take minus one characters from first position, that means you take nothing. So that is something for you to figure out how to fix it. But that's what is happening. Well, if you talk about other functions, for example, if I'm interested in doing some character translation, I can say translate.

And then if I gave as we discussed some iPhones, and then stars, ABC and again some stars, and if I say every star and hyphens should be replaced with a dot and then Something like underscore, then this is going to be what you get. And don't forget to change it to duel, because you want the result to be displayed only once there we are, all stars become dots, all hyphens become underscore. But if you remove this underscore, as we have seen, it's going to remove all occurrences of hyphens, they are gone. So you have only starts replace it with dot, this is character base in replacement or translation. But the same if you try to do with replace, and if you say you would like to replace every star or every double star with something like ABC then you can see this is replace it with ABC.

And this is replace it with ABC because it's looking for just to see Stars and those two stars are to be replaced it with ABC. It's not character based, it's a string based. So what it looks for is exactly two stars. Wherever you find two stars, it's going to replace that with whatever you gave. And as you have seen, the length of the strings need not be same. You can have different lengths for source and replace string.

So those are the string functions. So this lesson is all about how to use string functions. So practice them, get used to them, because we will be using all of them a lot all through. Well, that's all for now. We'll catch you in the next lesson.