There’s a length function, so length function gives you the length of a character string okay so you’ve got a length of a character string so I say select star from countries where the length of country name greater than 10 so it’ll only give us country names where the number of characters in the country name is 11 or more so we get these longer ones the shorter ones that don’t do not show up so the length is the length of a character string if you pass the length a numeric it’ll first convert it to a character string and then give you the length of that character string through an implicit conversion as we chatted about before other functions l-pad and our pad left pad and right pad there’s a lot of different syntax for that but basically the syntax take takes three arguments where the first argument is the source string so I’m going to pay it a string so here’s a string I want to pad the second argument represents the final length.
So I’m going to pay it a string like quote ABC but I’m going to pay it to 10 characters and the third argument is what you’re going to pair it with I’m going to pay it with an asterisk so that’s basically how that works here’s one thousand plus 200 points 55 is 1200 point 55 the padding pads character string so it converts that number to the character string 1 200 points five it’s left padding it to a length of 14 with the character asterisk that’s why you get all these asterisks in the front next page 187 here’s an example I’m going to write pad and I’m going to do the first name concatenated with space and last name so I get first name and last name and then I’m gonna k I’m going to write paired that to 18 characters and then I’m going to concatenate urns and concatenate a left pad of salary 26 with spaces so I’m going to have these faces and this result is actually incorrect as a typo in this book since I write padded the names to 18.
The name should be lined up the keyword earns should be lined up and the salaries should be lined up but they’re going to be lined up left padded so they’re going to start on the left with blanks and lined up on the right so the first name and last name should is lined up on the left but the keyword earns should be lined up the e4 earn should be lined up and then the last digit on the right should be lined up so there’s a typo here so you can just make a note of that in your book okay then is the trim function set up adding something you’re going to remove you can trim off a string and that removes by default remove spaces from both sides of the strength left and right or you can trim just trailing and a carrot that doesn’t have to be a space or following and a character in a space and then there’s also an l Trimm for left trim and an art room for right trim there’s a lot of stuff like that for trimming things.
So you need to look up the syntax for all of those if we look on page 189 here’s an example I have space last name and some spaces here and I’m comparing that to Smith that has no spaces on it so I do a trim of that this is kind of a bogus example because why would you I want to have spaces in the front and the end of the last name I’ll tell you why in a minute while we’re showing you this but the point is if let’s assume that last name smith some people put it in a Smith with a couple of blanks at the end you know and by mistake they type your last name of Smith with one extra blank and you know blanks when you type them are so narrow you can barely see them you don’t even know they’re there and they don’t display as anything so if you said we’re less name equals Smith and Smith had a blank on the end you wouldn’t get it Chris blanks count okay so blanks counsel you would not find that that row.
If it was SMI th with a blank on it if you compared it to Smith SMI th because every character counts including blanks so the way you would do that if last names might have blanks on the end or might have blanks on the front you could say we’re trim of less than equal Smith and then you would find it see the point there so this is kind of little bogus example but the real example would be if the Smith is actually in the last name and you want to find it any way that’s where it comes in handy any questions on that anybody okay now we come to 22 functions that are a little bit more complex because they have a lot of arguments one is in string and one is a substring in string and these two functions together can allow you to do any kind of string manipulation you want you to know sometimes you’ll get a part number and character positions seven eight nine represent the warehouse that the part is stored in or the type of point it is you know more likely the type of part it is okay.
So you might need to do an analysis by type of part but it’s built into the part number so you want to let’s say I sort it by type of part and you can’t sort it by part number because the type of part is the seventh eighth and ninth well you’re going to use a substring function to pull out the seventh eighth and ninth in and order it by the substring function of the part number so that’s the kind of thing substring is useful in string allows you to find one string and another string because sometimes you’re looking for something within the string so between the in string and the substring you can find a string in the string and once you find it you can pull it pull something out so even if you don’t know where it is you can locate it within string and then with substring you can pull it out and that comes in really handy in a lot of situations i mean the simplest for substring is substring a phone number 1 comma of the for the starting in character one position for three characters that’s the area code okay.
So you need to pull that out sign sometimes to do things with it in the string, ah it says has four parameters to are optional to a mandatory the in the string the first parameter is the source string what are we searching in the last name the first name we have a just a character string researching what are we searching it the second is what are we looking for the third is the start position where do we start looking by default we start looking in the position the start position B folds to one so the other start position it starts in the first character position but maybe you don’t want to start the first you want to start a little lady you want to start in a tenth of a 50th and the fourth one is the occurrence and by default you want to find the first occurrence okay so if you leave out the start string the search start position and if you leave out the nth occurrence they default to 11 starts in character position one finds the first occurrence okay.
So let’s look at an example here see if we have one within string here we go page 191 6 select star from departments wherein string of department name Oh n equal to so that says hey I’m looking in the department name I’m looking for an on I’m starting a character position one and I want to fern the first occurrence ah so it always returns the position of the first occurrence well if I want the o/n to start in position two I get all these things.