A control construction contract, contracting I wanted something where the o/n started in position to see if there’s another example it’s maybe a little bit more useful no that’ll be good we’ll see some others coming up so that’s in string you can find a position of where something starts substring also has multiple arguments okay takes three three parameters the first to a mandatory again it’s the source string the second one though is to start position where do you start okay the start position will default to one the next one is the number of characters to extract by default act everything from that point for to the end of the string so let’s see if we can find some examples of substring yep on page 192 towards the bottom I have advertising team member colon I got that down at the bottom I’m concatenated that with the first name starting in position 1 I’m substring that for one character well if I saw it in position one for one character what do i get i get the first letter of the name.
So i get advertising team member and then it’s jay s and L whatever the first letter of their first name is concatenated with a period and a space period in a space and concatenate that with last name and that’s initials and last name but I’m saying we’re a substring of job ID 12 so where the software the job ID starting a position one for two characters is an ad in uppercase ah and actually this looks like a typo ad in uppercase yeah this doesn’t look right because these are ad and so and the where clause would fail because this is in a little D so that should be an a little D not an eight no wait a minute say uh say it again you are it coming from the job ID you’re right it’s from the job ID not from not from the advertising okay so this is a different thing that’s coming from the job ID which is not even listed in here so we just have to assume that the job ID sorts with an ad in this case it would have been nice if they printed out the job ID so we can see that okay good point Thank.
So the other type mode so that’s the other type of that I don’t work advertising you what I don’t work in advertising when it Janeway land comes up oh and Jey Willie oh you’re not in advertising, yeah well maybe you are and you just don’t know if you know yeah nor never can tell okay good point Jay Waylon you know the authors of this book ah maybe okay it’s me it’s here maybe a John Watson is your pseudonym he’s one of the authors, okay so we’ve got that let’s look at page 190 okay oh the next function is a replace function takes a source string what you’re searching for what you’re replacing with which is optional if you do not have what you’re replacing it with it replaces it with no nothing so it actually removes the cert the replace becomes removed remove it completely if you don’t have a replacement term okay so here I’m replacing insist 8 I’m taking dec and replacing it with nov.
So that’s a query to so if I had november if I had december 17th as my sis date it replaces the Dec with a Nov I get november seventeenth to let’s see what we have here last name salary replace salary replaces 0 oh this is nice dream salary I love it this replaces 10 with three zeros all right so numeric salary it replaces expects a character so it converts the 14,000 to 14000 and then replaces each 0 with three zeros so I instead of three zeros I wind up with 90 okay so it’s a standard rebate your standard replace function and there are a couple other optional parameters in here which don’t look like it’s shown in the book see if there’s an example no so let’s look up the replace function Oracle replace function syntax reps let’s go syntax right doesn’t show you what I wanted to show you all right let me put that down as a question full replace syntax let’s check that out later okay so that’s our replace function.
What other example we have in here page 196 first name last name and substring of first name 11 it’s first initial with space and last name 14 14 so it starts the character position one and it gets only 14 characters so if the last name was longer than 14 it wouldn’t get any more than that and it says where length a first name plus length of the last name is greater than 15 so it only gets those where the length is longer I don’t know this doesn’t really it’s not really a very useful thing here but it reminds us of the length function of the first name it takes the number of characters and first name plus the number of characters and last name not very useful function so those were character functions the next thing we’re numeric functions any question I need the character functions especially the in string and substring and replaces okay numeric function is like round ah let’s look at an example here I am displaying system- hire date.
So that’s several days that someone has been hired and then I want to round it syst eight minus hire date that rounds it to the nearest integer so anything 4.5 and up goes to the next integer so 40 10 nine-point nine goes to 41 10 anything below that, unfortunately, they don’t have anything any example below that the role point nine nine seven but anything like a point 49 or whatever would round down on the round trunk goes the other way it truncates it so anything uh anything through anything less than the integer would go down to the next lower integer and modulus ah they have an example here they got an example of trunk probably modulus coming up and modulus is the standard modulus function where it divides by that number and it tells you the remainder so here’s salary x 1 point x 1.13 and so on for whatever reason, they’re multiplying that and then they truncate that so a point 07 goes down to 10 181 a point oh eight older does go down only.
The ones that are over point 5.5 or over like 88 23 will go up to 88 88 23 is this trunk or round oh that’s a salary this is the trunk we’re truncating so 88 23 goes down so everything goes down in this one because we’re doing a trunk so everything goes down to the next lower integer modulus mod of employee ID for means divided by 4 and give me the remainder if you got several 100 / 425 there’s no remained 101 remainders are 1 and so on standard modulus function okay let’s look at the dates now we have some date functions okay we know the standard date format we’ve seen that this shows a date format with slashes and you could set that up but by default Oracle generally does dashes not slashes but you could change that in the default capable default ability in the pro in sequel developer if you want it and you can change that in anything in the database as well default is normally the dash, not the slash as it shows they’re here okay.
When you’re doing date formats and your formatting data then you can format it with a format mask DD is the day of month mo n DD is a day of the month Emma when is the month of the year two-digit four-digit year Tujhe digit century year 2000 compliant HH is the hour HH 24 is a 24-hour mi is minute SS a second and there are a whole host of other things if you look up the documentation online you’ll see there’s the queue for the quarter there’s a Julian date for the day of the year pretty much anything you want to do with dates Oracle has it in their update format mass this is just a summary of the date format masks and for RR did the default year 2000 I mentioned yesterday or a couple days ago that there are all sorts of rules for that and here are all the rules I’m not going to go over them but you know over them all in detail but basically if the current year and the specified year are between 0 and 49 then it uses the current century.
So the car current year is between 0 and 49 and if you had a 15 it would use 2015 you had a 12-hour use 2012 he had a 70 used 2007 but if the two digits two-digit current years between 0 and 49 and the specified year of the date is between 50 and 59 is the previous century so that if we’re in 0 2 49 our current they if we got a 95 it would make it 1995, not 2095 and there are some other rules in here as well just to be careful.