What is implicit conversion?

 

The implicit conversion does not include time elements so that’s a key thing to remember a lot of people that’s something that gets confused a lot because you would think gee it should be able to convert the time and it doesn’t okay examples are using the functions and in the Select, so I mean these functions can be used in select statements that’s pretty much where we’re going to use it but they can be used in other sequel statements like update insert you know to delete that you can use these functions anywhere you can use sequel and you can use them in PL sequel if you’re writing code and PL sequel okay so some of these functions as the character manipulation or on pages 177 182 and so on so let’s take a look at 177 and I’m going to bring that up here chapter 4 177 ah let’s see I’m going to go all the way over to page 178 and let’s look at some of these character base functions first here I’m selecting the first name last name and lower of the last name. 

So notice here is the last name and the result make this a little bigger a little easier to see say 150 okay i’m going to display the lower of last name now this table is returning multiple rows five rows happens to be in this table and therefore the lower is applied to every last name in every row so remember a character these are these are single row functions they work on each row individually and therefore if there’s five rows the lower gets applied to the last name in each of the five rows and we’re also saying where lower of less name is like % you r % remember the like command percent is a wild card zero or more characters so anything with the you are anywhere in it and therefore we get a rise your insights hard one for me to pronounce floor I guess Scotty you are in war 0 and so on Herman anywhere in it notice erman starts in the very beginning but the % means zero or more characters are well in front of it therefore it works fine so that’s your that’s your lower function and notice. 

How you use the lower function sometimes if you don’t know how the last name is stored and you don’t know if it was actually in upper or lower case you’re going to say we’re lower of less name like you are if we didn’t say it if we just said lower of less name like % you are percent in in in quotes here that would look for the lower case you are so it would find the first four but it would not find Herman course notice erman here is an upper case you so if you didn’t do where lower of less name like you are you would not find the Armand so it’s a nice way with the lower or the upper would have worked if you checked it against the upper case you are to find data when you don’t know what the case is that it’s stored as in the database if you knew the case was always the lower case or always uppercase to be easy but often we don’t know that any questions on that concept ok that’s 178. 

Let’s look at 180 new book select star from countries where upper of a country name like % u % s % a finds any country and we don’t know what the case is so we’re going to do upper here and has to have you followed by an s followed by na with characters in between it so of course, we get the United States of America but we also get Australia clusters are you in there and there’s an S later in there and there’s a later in there if you had a country called a su which then that would not show up because this is the order any characters followed by you followed by any characters internets followed by any characters in there you can’t change the order of that you can have something that has an a before I mean there may be an a before you but they’re also asked being an after you that has to be it you followed by an S followed by a somewhere in that strength okay that was 180 lets’ look at 181 stop me if you have any questions on any of this I’m going pretty quickly on this stuff. 

Now select a knit cap and knit cap says in it capitalize the initial letter of every word so it looks at the last name so as a knit cap of the last name works as we’re concatenating job ID and we’re saying we’re a knit cap of the last name is like h % now notice I didn’t have to do an upper here I didn’t be of the last name and it would find any last names that happen to start with the lower case because the innate cap is converting the last name to something to the first letter of each word having an upper case H so here if we did this we get anything with an uppercase that starts with an uppercase h and then why is the W uppercase in works why is the uppercase and as to why is the application we didn’t say up a case here we should say lower case why is that because of the parentheses correct what the because of the parentheses this whole expression is evaluated first before it’s applied to the unit cap it’s not a knit cap of the last name. 

If I set a knit cap open paren last name close paren it would just a knit cap the last name but this is saying and knit cap the whole thing the last name concatenated with work as a concatenated with the job ID and a knit cap capitalizes the first letter of every word good okay ah the second one is a knit cap of the last name concatenated with a knit cap of works as a concatenated with a knit cap of job ID and basically we get the same result but we didn’t have to do that we didn’t have to knit cap each of them we can just concatenate them and every word will get uppercase so we get the same result either way okay page 182 it’s just another example that shows where we’re using the lower a first name we’re lower first name like and we don’t know the Li whether it’s uppercase or lowercase but we’re doing lower a first name so it converts every first name to lowercase. 

If I had a million rows in my employee’s table lower would work on at the first name of every one of those million rows it function takes an argument it’s a single row function, therefore, it applies that that function to whatever that argument is, in this case, the first name to every row um let’s look at as a function called can cat which really I am not very useful it takes that you can use this instead of the two vertical bars for concatenation so I can say here select can cat of today is a comma sustained so since the date is the date but it’ll do it can cat takes two character arguments so the date will be implicitly converted to an appropriate date and let’s say it was december 17 2007 so you’ll get today is 17 december two thousand seven okay because it’ll gankin cat takes only two arguments only two arguments same thing here can cat concatenate two strings this is not a string it’s the number 2 plus a 1 plus 2.14 is 3.14 you can’t concatenate that because it’s a number.

Oracle implicitly converts it to The string 3.14 and concatenates that with space approximate approximates pie the problem with can cat is when you have more than two arguments if you have three arguments you have to take two of them and concatenate those and then running another can cat to concatenate that with a third argument so you’ve got can cat multiple times you know building in an internet or if you had seven things concatenated if you get a little silly instead of doing concat you can just have two vertical bars and you don’t have any of these problems that would in that case you would have outer one concatenated with inner one concatenated within or two you’d be done but just to let you know that concat function is there ah here’s your can cat first name with a concatenate and a concatenate all all to get the same result first name can cat away that I need I need for things concatenated here so it’s got a lot of can cats instead I can just use the vertical bars and I’m good gets the same result or concatenate it nicely.

Leave a Comment