Built-in Functions in MySQL

So create alter drop truncate and so these are all called data definition language commands Okay. Now the topic is built-in functions in mysql built-in functions in Mysql or you can say mysql. Okay, so built-in functions in mysql. So what is built-in function? So built-in function is a kind of a keyword. We can pass some parameters and it will produce some output Okay, so we have n number of functions are available. So we are going to see what are the different type of functions available? In my sequel so they are built-in functions. So my sequel built-in functions. So the function what is function? So function is a basically a keyword or we can if you can pass some parameter to the function Then it will return some output for us. Okay So there are four types of functions that are available in MySQL. So string functions, numeric functions, date functions and aggregate functions. So these functions will be varied from one database to another database. But the name of the functions may be slightly different. But 90% of the functions are almost same in every database. If you go for Oracle or SQL Server or DB2 or if you go for any other databases, almost 80 to 90% of the functions are having same kind of syntax. same kind of usage and There may be some slight differences will be there irrespective of number of parameters or the name of the methods Okay, so suppose in one database. There is a method called LEN or some other database it will be length LEN GTH The name can be different but the functioning and behavior of the functions are exactly the same So in my sequel especially we have a four kinds of a built-in functions are available string functions numeric functions date functions aggregate functions so these are the four categories so string functions works on string data type of string data basically string type of data and numeric functions works on numeric type of data and date functions are works on especially date type of columns and aggregate functions we use normally to perform certain kind of calculations okay so these are the four types of functions available in my sequence so now let us discuss one by one each category Now we'll start with the string functions So in string functions, we have different type of functions like upper lower length trim Nstr and so on. So let us go through one by one. So the first one is upper function So what is the upper function will do is this function will convert any characters into uppercase characters? Any characters into uppercase characters? similar lower function will converse any lowercase letters into uppercase characters okay lower and upper so let's use this command for in our table so this is syntax which we need to use upper and lower let's go to uh workbench on here again i'm switching back to the hrdb so i'll just say use hr i think currently it is pointing to hrdb so it is highlighted here so now i'm executing this command select so normally when i say select first name from Employees select a first name from employees when I execute this particular command When I execute this particular command it will give you all the first names from the employee table It will give you all the first name from the employee table. Now. I want to display all this data in uppercase letters Okay, so now I can use upper method here upper function upper of first name So what this upper function will do is this will convert this will convert The column values whatever values we have in this particular column all the values will be converted into uppercase Okay, so before that I can just show you how to use uppercase first normal variable I'll just say select upper off in the single quotations. I am giving some name called Smith Okay, just execute only this command select the upper of Smith So when I execute this it is giving uppercase characters because here we pass lowercase characters Here it is returning uppercase characters. Same thing if I use upper function in your query, it will also perform the same thing. So because first name is a column name, which contains a many number of many number of values. So all the values in this particular column will be changed into upper. So change means it will not change in the table, it just present the data. So in the presentation, it will appear in the uppercase, but in the table, whatever the format it is there previously, it's still there. so upper and lower functions will convert the data into upper case as well as lower case so same command i'm executing for with the lower case Say lawyer. This is another command. We have is another method So when I do this one first one, it will give you all the first names in uppercase letter See this all uppercase letters now when I use this command, it will show you all of them is a lower case letters But in this case, I don't have a column. I have only single value. So the upper function box only on the single value Okay, so this is how upper and lower functions will be used So to convert any type into lowercase for uppercases, so the next one is the length function So this will length it will return the length of a string. So this function will return length of a string So for example when I write like this Select length off and here I'll put some string like this welcome So select length of welcome. So this function will count how many characters are there in this particular string? So when I execute this it will return how much so it is not returning anything one second It should return something. So okay, so function HR dot length does not exist I think spelling is wrong le n g th guys Okay, g th now execute this now we got the seven So this function will count number of characters inside this particular string suppose if I apply the same method on the particular column So for example here, I'll say first name instead of variable as a first name column from employees table from Employees table. So now what happens is this butler function will count the length of all the first names in that particular table So when I execute this it will give you only length here So the length of first name so first name length is 5 second name length is 6 third name length is 5 It will display the lengths of all the first names in a table. Okay, so length function will find the length of a string That is the purpose of length function and suppose I want I have a simple query guys So what this query will do is my query is I want to print the names of employees Whose names is exactly four whose names are the characters in their names should be four or should be five So how we can extract the data? My requirement here is I want to print this my query. Okay, so I want to print names of employees names of employees whose name has Four characters whose names has four characters. I want to print names of employees whose names has four characters So how we will find out how many characters we have means we need to find out like this So select staff from employees. It will give you all the data where The condition like this length of first name so what this will give you it will give you the length of a name Right so length of first name is equal to 4 so it will give you all the data But it will apply this particular filter What is filter it will go and check each and every first name and check that length is 4 or not If it is 4 then it will display as a record Okay, so when I execute this code it will display only the records and all the first names have started with a 4 Having only four characters every first name is having four characters only. Okay, so like this We can use this particular length function So length of first name all we can use any other column inside this particular length function So this is our important query next one trim function So trim function just removes the specified character from the both the sides so we can use this same function in two different ways I'll show you some example here. So select trim off So here I have a string which can here right side house on spaces and left side is also some spaces I have let me just change this. Let's say welcome. So right side as well as left side. I have spaces, right? So now what I can do is if I just execute this butler command, so I'm removing all these things So this is just a command case. Okay, select a trim of a string. So right side and left side we have a some We have some spaces right so now what I can do is I let's execute this command Now we can see it is removed right as well as left side spaces So the trim command will trim the right as well as the left side spaces and it will return the exactly The string and we can also use trim function in different way Suppose if you see here in this particular syntax select a trim of jet from this particular string So what it is saying here is in this particular string wherever jetty is there how many times jetty is repeated one two three four times jet is repeated so it will trim the jet from the string basically it will remove the jet from this particular string and it will return the exact string without after removing the jet so when I give up this command now you can see it is returning only Oracle and here to Jersa removed and here to Jersa removed so right as well as left side strings will be removed by using trim function we can remove the Spaces or characters from right and left designs. Okay. So this is a dream function usage so the next function is nstr function, so nstr function returns the Position of a character within a string and nstr function returns the position of a character within a string So what does it mean is suppose I have a string like this oracle. So I want to Get the position where the ease exists. So let me execute this command here select I'll say some welcome here. So this is my string So I want to check where the is exactly present in this particular string. What is the exact position where the ease occurred? So when I execute this command, it will give you two so in the second place first time the ease occurred So this is one and the second time So he started in the second time second place suppose if I say over here, okay, I say over here So what is the location of 4 1 2 3 4 5 so it should return 5 now when I explore this It should return 5 here. So I n str function will return the Position of a character in a string so I n star function will return the position of a character within a string Okay So that is one of the function we have so upper lower length trim and any SDR So we have few more functions sub SDR or substring So both are correct you can go for sub SDR or you can use substring both will do the same job So what is the sub SDR and substring? So this will return the substring of a string It will returns the substring of a string. So let me show you how we can use this sub SDR or substring Both will do the same job. So now select the sub base TR of I say some string called oracle And here we to pass a starting position and ending position. So see this guys So these three are sub base TR to the below ones are sub string So both will do the same job and both the functions will take three parameters one is a string parameter Second is a starting value third was an ending value From where to where we need to extract the portion of a string. So here's our best er 2 comma 3 So what is 2 here 2 means it will start from 1 1 and 2 in the second position What is there are from there? It will count to 3 characters So 1 2 3 so including R So the second place R is there from there it will count 3 characters R is 1 A is 2 C is 3 so our AC is written here So this command will return our AC. So it is written RAC and suppose when I say a 3 comma 3 now So 3 comma 3 means third position 1 2 3 in the third position We have a from there. We should count three characters So a is 1 C is 2 L is 3 is 4 so a CL A CL is written. So only those are three characters now. I say 4 comma 3 4,3 means it will start from the fourth location 1 2 3 4 the fourth location We have a C from there three characters. So C L E three characters will be written. So Cl is there Okay, let's execute this command. So here I fun I fun is a just a comment guys So comment means I fun I fun after that whatever you have written here that will be not considered at the time of execution Even if a combined this execute it will not perform anything. So this is just ignore the statements My secret will ignore the comments at the runtime, right? So now when I execute this it will return a CL and when I execute this it will return Cl so fourth position to from there It will count three next characters and the sub string also will do the same thing. So nothing is a difference So yeah, it's good. Are you see it is returning ACL and this is returning Cl So these are all substring functions. So how to use this function? So let's go to the table now in the table So we have a first name string. So select a star from Or we can say selector first name from Employees select the first name from employees So when I execute this particular statement, it will give you all the first names of employees now from all the first names of employees I want to print only first three characters. I want to print only first two three characters of every employee in this table So how we can do that? So simply use some best here are some substance function as a sub string of sub string of First name and how many characters you want to print from the one two from the one to only three characters I said one two three. I want to paint some employees So now execute this this will give you all the first names, but only first three characters will be printed in the table So this is how we need to use sub string or sub base TR function So these functions will applicable only on the strings. All right, so now There is another method called concat method concat method. So what is use of concat method? So concat method will join the two strings. So for example when I use this command So concatenation, so let's see select the concat of this is a one string. This is another string So here we need to put proper brackets Single quotation so oracle is having one string. This is another string So when I execute this command, it will concatenate both the things so oracle training so both are completed both are concatenated Suppose if I want to concatenate first name and last name so in my table Select star from employees. Let's say select first name first underscore name So let's say select first underscore name and last underscore name from employees So I'm just getting first name and last name from the employees table. So it is printed in the two columns So now now I want to print only one column which contains the first as well as last name I want to print only one column which contains the first name followed by last name So I need to concatenate them. So how we can do this by using concat function we can do that So here just apply concat functions concat of first underscore name last underscore name Employees so now when I execute this command it will give you so it is giving some error Let us see what is an error is giving select concat first name last name from employees function hr concat does not exist Sorry guys spelling mistake Concat right so can get so let's execute this command one more time. So now it is successfully executed So now we see the data here see the data so can get our first name last name we got all the first name last name combinations, okay last name first name combinations Suppose I want to change this presentation that here it's not giving properly right can get our first name and last name So whatever we have done in the query the same thing it is showing here But instead of printing this I want to just print a full name of employee full name I want to print as a column name just specify the alias name I'll say full name. So this is just a alias of this particular column So now when I execute this query, then it will give you full name as a column name This is just alias name. Now here we have concatenated first names and last names and presented in the table Okay, so this is just a presentation guys in the table will not be affected any data or any table structure This is only presenting the data. Okay, so now This is about concatenate function. So these are all string function ways So for whatever discuss the string function there are hundreds of functions are there and these are very frequently used methods most important methods And if you want to refer whatever other functions are available, you can just go through this link So my sequel official website they have provided lot of Documentation for this so go to just open this URL In this particular page they have provided all the same functions and also description also given and examples also given If you want to know one of this method how to use this particular method you have to just click on the proper method So for example, if I click on the upper it will they will give you the documentation how to use this particular command all these things Okay, so this is simple page. They have given there are so many methods are there in each and every category hundreds of methods Okay, you can guys can be from this one. So these are all most used String functions in sequence. So now we'll move on to the next category number functions or we can also say numeric functions So these functions are used only only on the number columns or numeric columns So now we'll see what are all functions are available in sequel. What are our number functions are available in sequel? So the first function is ABS. It will give you absolute value It will just remove the minus and place all these things. It will just give you absolute value So when I execute these two commands, so minus 40 when I do this, it will give you 40 only Even it is executed this it will give you all the 40. It will just give you absolute value of the number and square root is there it will give you a square root of the value and it will give you a reminder so let's say 10 comma 3 so 10 divided by 3 what this will return 1 okay and then power select power 2 comma 5 and truncate function and there are other functions also available so these functions are called as a numeric functions now we'll see one by one all right so now we'll see one by one function how these things will work Let me just copy these things go here. So absolute function means it will give you absolute value here so 40 even if you give an entry value of positive value, it will return the positive value and Next one is fake square root. So when I pass some value here, it will give you a square root of 25 and 10 comma 20 It will perform division. So 10 divided by 3 perform You get the remainder as 1 and this is a power. So power means it will perform 2 to the power of 5 2 into 2 into 2 into 2 into 2 5 times it will multiply so this will give you 2 to the power of 5 32 is a value so these are just mathematical functions guys so if you want to perform any mathematical calculations in the query you guys can use these functions so and then truncate functions also so what is truncate function is so the truncate function is very important so truncate function truncates a number to the specified number of decimal places So whatever the truncate command we discussed that is different. So truncate command is comes under DDL So there's a different here. I am talking about truncate function. Okay truncate function in database So truncate function truncates the number to the specified number of decimal spaces So I will show you a few examples then you will understand what exactly truncate will be used. So let's see the first query So select a truncate of 40 comma 1 2 3 4 comma 3 I have given two parameters We need to provide so what this function will do is so I say 3 right? So it will just have only three values after the decimal and whatever the other digits are there that will be removed That will be truncated. So 40 dot 1 2 3 4 is the actual value I am saying 3 here. So 3 means after decimal only three digits will be there and rest of the digits will be removed So that's the reason it is it is returning 40 dot 1 2 3 40 dot 1 2 3 only 3 digits are Preserved and rest of the digits will be removed now the next function. I'll say 3 here I'll say 2 here now after decimal it will have only 2 digits and 32 for 34 will be removed and then truncated So it will give you 40 dot 1 2 and we can also pass the negative values here Instead of positive you can also negative values we can do so when I pass negative value what happens So select a truncate of six eight seven six I have not given any decimal number when I say minus one when I say minus one the last digit Whatever it is there that will becomes a zero. So last it is it one digit will becomes a zero So it will give six eight seven zero and when I say minus two here Last two digits will becomes a zero so it will return six eight zero zero Similarly if I say last minus five so in this particular number when I say minus five Last five digits becomes zero and then it will return six seven six eight seven rest of them or zero So this is a behavior of truncate function. So truncate function will basically truncate the Values based on the value we provided here We can provide the positive value. We can also provide the negative value Okay, so this is about truncate function which we can use it on numbers only right? So ABS square root mod function power function and truncate function and few more functions We have greater greatest and least functions So gated greatest is a function which will find a greater number among this particular list and least is another function Which will find the lowest number in this particular list? So greatest and least values will be returned by these functions. So when I execute this here select the greatest of this one So I have 100 200 3 4 500. So when I execute this it will give you 500. That is a greatest value You can keep this number anywhere. So finally it will return only greatest value And when I execute this command this will give you the least value that is 100 so greatest and least So these functions will take some of the numbers some list of numbers we need to pass All right. So the next function these are all number functions Okay, so so far we discussed string functions and number functions now we'll move on to the date functions So these are very important to perform the date related actions So we have some date functions. So what are those date functions? So current date and current underscore date So these are the two functions will work in the same way. So select the current date and current underscore date So this particular function will returns that current date these functions will return the current date So let's say select a current date. So this will also return the same thing. So here month and date Similarly current underscore date also will return the same thing Year month and date. So these two commands will return the current current date present date Now we have another two commands current time So these two commands also same these two methods also same and these two methods will return the current time These two methods will return the current time only time select the current time select the current underscore time So when I execute this it will give you current time and this is also give the same thing So current date and current time we can retrieve by using these commands or these methods now when I say now It will give you date as well as time both so when I say select now So this will give you date along with the time both will give you so if you want to give or if you want to get Only date go for this one if you want to get only time Then go for this command if you want to get date as well as time then use this now command Then sis date is also there. This will also return the system time system date and in system whatever date you have configured So that butler date will be returned and here I need to use select the system select Select sis date. So this command also will give you system date. So this is our current system date and So there are few more months. So there is one more method called month year day month is a function which will retrieve the month value from the complete date and Here is a method which will retrieve the year value from the completed and day function will retrieve the day value from the complete Complete date. So that's the use of month year and day There are a few more methods month and the next one is here and then next one is date Day the current day. So let's try to execute this over here 2009 this is a value have passed 2019 0 5 and 19 have passed so when i pass this data so this will give you 5 so what is this 5 is month so month is extracted now in the second query it will extract the year so 2019 is the year it is extracted now in the third query it is extracted the day so that is 19 is a day which is extracted so these are the different functions available in the date functions And also there are so many date functions are available. We want to refer all of them. Just you can go through this link. So this link is provided all kinds of date functions which are available in MySQL. There are huge list guys. And we can also perform lot of actions like we can find out the number of days between two days. And we can add the next date to the existing date. We can do minus, plus, all the operations we can perform on the date type of data. So these are all the different functions. There is a huge list is available. So we can just go through each and every method. They will give you clear documentation how we can use those functions or methods All right. So this is all about date functions guys date functions So now I have two important queries here based on the date my query is So in the employees table, I have list of employees. I need to filter those employees. I need to print the data of the employees Who who is joined in 1987 in theory? So I want to print the employees data whoever is joined in this particular year 1987 so I want to write a query like this. So I want to print the data of employees Whoever is joined 1987 in the exact year So select the staff and employees will give you all the data and to filter the data We use where condition and when I say hi Did is there in the select staff and employees when I say hi, did is column is there select to start from? So select the staff and employees table we have high grade column. So if you see here high date column is there So here, where I say select a staff and employees where, what I am saying, what is my requirement is, I want to check the employees joined in the 1987 exactly in the year or not. But in the higher date, 1987 is there. But along with this, the month and date is also there. I don't require that. I need to compare the date with only the date part in the higher date. So how we can extract the year part from this? I need to get the year part by using year function. So select star from employees where year of high date So this command will extract the year value from this particular date and then it will compare with the 1987 or not If this condition is true, then those records will be retrieved So when I execute this it will give you employees. We have joined exactly 1987 so now if you see the high date here all the years are 1987 okay, so this is how we need to use these functions There is another so display employees who have were joined in the June. So here I'm specifically say month name Okay month name and specify so display employees who are joined in June So this query we can write in two different ways. So one is we can write like this. So the first query is Select a star from employees where month is a method so month of high date. So this method we discussed Because what is this method will do this method will extract the month from this date So month from this date and that month is available in the form of number Sorry in the form of a number like this. It is a string only but into inside. This is a number So this command will return the six suppose if it is june six is written so i'm comparing with the six So this will also give you the employees. We have joined in the sixth month. So now we can see all our six suppose I want to give the Name of the month how we can do that if you want to provide them a number of the month You can directly use month function here month of high rate is equal to 6 Suppose if you want to send name of the month then how we can compare so we have another method called month name So this will return the name of the month from this high date corner So for every date value it is extracted month name based on the value and that month name should be equal to June Now this query also will give you the same data. So all the employees have joined in the same month Okay, so this is how we need to use a date functions. It's very very important functions So now the next category is aggregate functions. So aggregate functions basically Used to perform certain calculations on multiple rows of a single column of a table multiple rows of a single column of a table these functions will return a single value so aggregate functions will always return a single value remember this aggregate functions will return always single value and these functions required multiple rows on single column so for example in my table okay so I have employees table here let's say select star from employees table so in this table I have some data so what i want to do is i want to find out the salaries like a sum of all the salaries total sum of all the salaries or i want to find out average salaries of these employees okay or i want to find out maximum salary of the employee or minimum salary of the employees what is the minimum salary of these employees then we need to go for aggregate functions because these functions works on only single column but it is considering all the rows single column All the rows so when I say max of salary column Then it will give you maximum salary when I say minimum of salary It will give you a minimum salary average and some awesome. So these are the four functions We have a BG some minimum maximum and count count will count how many number of records are there in that particular column? So let me execute these commands. So the first function is average function suppose Want to find out average salary of these employees all employees means all the rows So I want to find out average salary of all the employees now select a BGF salary From employees and I execute this it will give you average salary of employees This is the average of salary Similarly if I want to get to some of all the salaries of employees now your salary of some of salary So here again multiple rows a single column Okay, this is the sum of all the salaries of employees now What is the minimum salary of employee and I execute this it will give you minimum salary 7 by so starting salary is 2100 Now this will give you maximum salaries of employees. So 24,000. Now if you want to use count star function, it will count how many employees are there in the table. So this will give you count. 107 records are available in the table. So these are all aggregate functions. So aggregate functions will work only on the single column. And if you notice here, everywhere I specified only single column. But also it is retaining single value. But this needs multiple rows. So this is all about aggregate functions, so we have discussed how many functions we have discussed String functions and numeric functions and date functions then aggregate functions So string functions works on string data type numeric functions will work on numeric data types date functions will work on data Data data types and aggregation functions will work on the number of rows of single column Okay, it will return the single value so these are all built-in functions which are available in mysql so this is all about functions concept in mysql

So built-in function is a kind of a keyword. We can pass some parameters and it will produce some output Okay, so we have n number of functions are available. So we are going to see what are the different type of functions available?

In my sequel so they are built-in functions. So my sequel built-in functions. So the function what is function?

So function is a basically a keyword or we can if you can pass some parameter to the function Then it will return some output for us. Okay So there are four types of functions that are available in MySQL. So string functions, numeric functions, date functions and aggregate functions. So these functions will be varied from one database to another database.

But the name of the functions may be slightly different. But 90% of the functions are almost same in every database. If you go for Oracle or SQL Server or DB2 or if you go for any other databases, almost 80 to 90% of the functions are having same kind of syntax. same kind of usage and There may be some slight differences will be there irrespective of number of parameters or the name of the methods Okay, so suppose in one database.

There is a method called LEN or some other database it will be length LEN GTH The name can be different but the functioning and behavior of the functions are exactly the same So in my sequel especially we have a four kinds of a built-in functions are available string functions numeric functions date functions aggregate functions so these are the four categories so string functions works on string data type of string data basically string type of data and numeric functions works on numeric type of data and date functions are works on especially date type of columns and aggregate functions we use normally to perform certain kind of calculations okay so these are the four types of functions available in my sequence so now let us discuss one by one each category Now we'll start with the string functions So in string functions, we have different type of functions like upper lower length trim Nstr and so on. So let us go through one by one. So the first one is upper function So what is the upper function will do is this function will convert any characters into uppercase characters? Any characters into uppercase characters? similar lower function will converse any lowercase letters into uppercase characters okay lower and upper so let's use this command for in our table so this is syntax which we need to use upper and lower let's go to uh workbench on here again i'm switching back to the hrdb so i'll just say use hr i think currently it is pointing to hrdb so it is highlighted here so now i'm executing this command select so normally when i say select first name from Employees select a first name from employees when I execute this particular command When I execute this particular command it will give you all the first names from the employee table It will give you all the first name from the employee table.

Now. I want to display all this data in uppercase letters Okay, so now I can use upper method here upper function upper of first name So what this upper function will do is this will convert this will convert The column values whatever values we have in this particular column all the values will be converted into uppercase Okay, so before that I can just show you how to use uppercase first normal variable I'll just say select upper off in the single quotations. I am giving some name called Smith Okay, just execute only this command select the upper of Smith So when I execute this it is giving uppercase characters because here we pass lowercase characters Here it is returning uppercase characters.

Same thing if I use upper function in your query, it will also perform the same thing. So because first name is a column name, which contains a many number of many number of values. So all the values in this particular column will be changed into upper.

So change means it will not change in the table, it just present the data. So in the presentation, it will appear in the uppercase, but in the table, whatever the format it is there previously, it's still there. so upper and lower functions will convert the data into upper case as well as lower case so same command i'm executing for with the lower case Say lawyer. This is another command. We have is another method So when I do this one first one, it will give you all the first names in uppercase letter See this all uppercase letters now when I use this command, it will show you all of them is a lower case letters But in this case, I don't have a column.

I have only single value. So the upper function box only on the single value Okay, so this is how upper and lower functions will be used So to convert any type into lowercase for uppercases, so the next one is the length function So this will length it will return the length of a string. So this function will return length of a string So for example when I write like this Select length off and here I'll put some string like this welcome So select length of welcome. So this function will count how many characters are there in this particular string? So when I execute this it will return how much so it is not returning anything one second It should return something.

So okay, so function HR dot length does not exist I think spelling is wrong le n g th guys Okay, g th now execute this now we got the seven So this function will count number of characters inside this particular string suppose if I apply the same method on the particular column So for example here, I'll say first name instead of variable as a first name column from employees table from Employees table. So now what happens is this butler function will count the length of all the first names in that particular table So when I execute this it will give you only length here So the length of first name so first name length is 5 second name length is 6 third name length is 5 It will display the lengths of all the first names in a table. Okay, so length function will find the length of a string That is the purpose of length function and suppose I want I have a simple query guys So what this query will do is my query is I want to print the names of employees Whose names is exactly four whose names are the characters in their names should be four or should be five So how we can extract the data?

My requirement here is I want to print this my query. Okay, so I want to print names of employees names of employees whose name has Four characters whose names has four characters. I want to print names of employees whose names has four characters So how we will find out how many characters we have means we need to find out like this So select staff from employees. It will give you all the data where The condition like this length of first name so what this will give you it will give you the length of a name Right so length of first name is equal to 4 so it will give you all the data But it will apply this particular filter What is filter it will go and check each and every first name and check that length is 4 or not If it is 4 then it will display as a record Okay, so when I execute this code it will display only the records and all the first names have started with a 4 Having only four characters every first name is having four characters only.

Okay, so like this We can use this particular length function So length of first name all we can use any other column inside this particular length function So this is our important query next one trim function So trim function just removes the specified character from the both the sides so we can use this same function in two different ways I'll show you some example here. So select trim off So here I have a string which can here right side house on spaces and left side is also some spaces I have let me just change this. Let's say welcome. So right side as well as left side.

I have spaces, right? So now what I can do is if I just execute this butler command, so I'm removing all these things So this is just a command case. Okay, select a trim of a string. So right side and left side we have a some We have some spaces right so now what I can do is I let's execute this command Now we can see it is removed right as well as left side spaces So the trim command will trim the right as well as the left side spaces and it will return the exactly The string and we can also use trim function in different way Suppose if you see here in this particular syntax select a trim of jet from this particular string So what it is saying here is in this particular string wherever jetty is there how many times jetty is repeated one two three four times jet is repeated so it will trim the jet from the string basically it will remove the jet from this particular string and it will return the exact string without after removing the jet so when I give up this command now you can see it is returning only Oracle and here to Jersa removed and here to Jersa removed so right as well as left side strings will be removed by using trim function we can remove the Spaces or characters from right and left designs. Okay.

So this is a dream function usage so the next function is nstr function, so nstr function returns the Position of a character within a string and nstr function returns the position of a character within a string So what does it mean is suppose I have a string like this oracle. So I want to Get the position where the ease exists. So let me execute this command here select I'll say some welcome here.

So this is my string So I want to check where the is exactly present in this particular string. What is the exact position where the ease occurred? So when I execute this command, it will give you two so in the second place first time the ease occurred So this is one and the second time So he started in the second time second place suppose if I say over here, okay, I say over here So what is the location of 4 1 2 3 4 5 so it should return 5 now when I explore this It should return 5 here.

So I n str function will return the Position of a character in a string so I n star function will return the position of a character within a string Okay So that is one of the function we have so upper lower length trim and any SDR So we have few more functions sub SDR or substring So both are correct you can go for sub SDR or you can use substring both will do the same job So what is the sub SDR and substring? So this will return the substring of a string It will returns the substring of a string. So let me show you how we can use this sub SDR or substring Both will do the same job.

So now select the sub base TR of I say some string called oracle And here we to pass a starting position and ending position. So see this guys So these three are sub base TR to the below ones are sub string So both will do the same job and both the functions will take three parameters one is a string parameter Second is a starting value third was an ending value From where to where we need to extract the portion of a string. So here's our best er 2 comma 3 So what is 2 here 2 means it will start from 1 1 and 2 in the second position What is there are from there? It will count to 3 characters So 1 2 3 so including R So the second place R is there from there it will count 3 characters R is 1 A is 2 C is 3 so our AC is written here So this command will return our AC. So it is written RAC and suppose when I say a 3 comma 3 now So 3 comma 3 means third position 1 2 3 in the third position We have a from there.

We should count three characters So a is 1 C is 2 L is 3 is 4 so a CL A CL is written. So only those are three characters now. I say 4 comma 3 4,3 means it will start from the fourth location 1 2 3 4 the fourth location We have a C from there three characters. So C L E three characters will be written. So Cl is there Okay, let's execute this command.

So here I fun I fun is a just a comment guys So comment means I fun I fun after that whatever you have written here that will be not considered at the time of execution Even if a combined this execute it will not perform anything. So this is just ignore the statements My secret will ignore the comments at the runtime, right? So now when I execute this it will return a CL and when I execute this it will return Cl so fourth position to from there It will count three next characters and the sub string also will do the same thing. So nothing is a difference So yeah, it's good. Are you see it is returning ACL and this is returning Cl So these are all substring functions.

So how to use this function? So let's go to the table now in the table So we have a first name string. So select a star from Or we can say selector first name from Employees select the first name from employees So when I execute this particular statement, it will give you all the first names of employees now from all the first names of employees I want to print only first three characters.

I want to print only first two three characters of every employee in this table So how we can do that? So simply use some best here are some substance function as a sub string of sub string of First name and how many characters you want to print from the one two from the one to only three characters I said one two three. I want to paint some employees So now execute this this will give you all the first names, but only first three characters will be printed in the table So this is how we need to use sub string or sub base TR function So these functions will applicable only on the strings. All right, so now There is another method called concat method concat method.

So what is use of concat method? So concat method will join the two strings. So for example when I use this command So concatenation, so let's see select the concat of this is a one string.

This is another string So here we need to put proper brackets Single quotation so oracle is having one string. This is another string So when I execute this command, it will concatenate both the things so oracle training so both are completed both are concatenated Suppose if I want to concatenate first name and last name so in my table Select star from employees. Let's say select first name first underscore name So let's say select first underscore name and last underscore name from employees So I'm just getting first name and last name from the employees table. So it is printed in the two columns So now now I want to print only one column which contains the first as well as last name I want to print only one column which contains the first name followed by last name So I need to concatenate them.

So how we can do this by using concat function we can do that So here just apply concat functions concat of first underscore name last underscore name Employees so now when I execute this command it will give you so it is giving some error Let us see what is an error is giving select concat first name last name from employees function hr concat does not exist Sorry guys spelling mistake Concat right so can get so let's execute this command one more time. So now it is successfully executed So now we see the data here see the data so can get our first name last name we got all the first name last name combinations, okay last name first name combinations Suppose I want to change this presentation that here it's not giving properly right can get our first name and last name So whatever we have done in the query the same thing it is showing here But instead of printing this I want to just print a full name of employee full name I want to print as a column name just specify the alias name I'll say full name. So this is just a alias of this particular column So now when I execute this query, then it will give you full name as a column name This is just alias name. Now here we have concatenated first names and last names and presented in the table Okay, so this is just a presentation guys in the table will not be affected any data or any table structure This is only presenting the data.

Okay, so now This is about concatenate function. So these are all string function ways So for whatever discuss the string function there are hundreds of functions are there and these are very frequently used methods most important methods And if you want to refer whatever other functions are available, you can just go through this link So my sequel official website they have provided lot of Documentation for this so go to just open this URL In this particular page they have provided all the same functions and also description also given and examples also given If you want to know one of this method how to use this particular method you have to just click on the proper method So for example, if I click on the upper it will they will give you the documentation how to use this particular command all these things Okay, so this is simple page. They have given there are so many methods are there in each and every category hundreds of methods Okay, you can guys can be from this one.

So these are all most used String functions in sequence. So now we'll move on to the next category number functions or we can also say numeric functions So these functions are used only only on the number columns or numeric columns So now we'll see what are all functions are available in sequel. What are our number functions are available in sequel? So the first function is ABS.

It will give you absolute value It will just remove the minus and place all these things. It will just give you absolute value So when I execute these two commands, so minus 40 when I do this, it will give you 40 only Even it is executed this it will give you all the 40. It will just give you absolute value of the number and square root is there it will give you a square root of the value and it will give you a reminder so let's say 10 comma 3 so 10 divided by 3 what this will return 1 okay and then power select power 2 comma 5 and truncate function and there are other functions also available so these functions are called as a numeric functions now we'll see one by one all right so now we'll see one by one function how these things will work Let me just copy these things go here. So absolute function means it will give you absolute value here so 40 even if you give an entry value of positive value, it will return the positive value and Next one is fake square root. So when I pass some value here, it will give you a square root of 25 and 10 comma 20 It will perform division. So 10 divided by 3 perform You get the remainder as 1 and this is a power.

So power means it will perform 2 to the power of 5 2 into 2 into 2 into 2 into 2 5 times it will multiply so this will give you 2 to the power of 5 32 is a value so these are just mathematical functions guys so if you want to perform any mathematical calculations in the query you guys can use these functions so and then truncate functions also so what is truncate function is so the truncate function is very important so truncate function truncates a number to the specified number of decimal places So whatever the truncate command we discussed that is different. So truncate command is comes under DDL So there's a different here. I am talking about truncate function.

Okay truncate function in database So truncate function truncates the number to the specified number of decimal spaces So I will show you a few examples then you will understand what exactly truncate will be used. So let's see the first query So select a truncate of 40 comma 1 2 3 4 comma 3 I have given two parameters We need to provide so what this function will do is so I say 3 right? So it will just have only three values after the decimal and whatever the other digits are there that will be removed That will be truncated. So 40 dot 1 2 3 4 is the actual value I am saying 3 here. So 3 means after decimal only three digits will be there and rest of the digits will be removed So that's the reason it is it is returning 40 dot 1 2 3 40 dot 1 2 3 only 3 digits are Preserved and rest of the digits will be removed now the next function.

I'll say 3 here I'll say 2 here now after decimal it will have only 2 digits and 32 for 34 will be removed and then truncated So it will give you 40 dot 1 2 and we can also pass the negative values here Instead of positive you can also negative values we can do so when I pass negative value what happens So select a truncate of six eight seven six I have not given any decimal number when I say minus one when I say minus one the last digit Whatever it is there that will becomes a zero. So last it is it one digit will becomes a zero So it will give six eight seven zero and when I say minus two here Last two digits will becomes a zero so it will return six eight zero zero Similarly if I say last minus five so in this particular number when I say minus five Last five digits becomes zero and then it will return six seven six eight seven rest of them or zero So this is a behavior of truncate function. So truncate function will basically truncate the Values based on the value we provided here We can provide the positive value. We can also provide the negative value Okay, so this is about truncate function which we can use it on numbers only right? So ABS square root mod function power function and truncate function and few more functions We have greater greatest and least functions So gated greatest is a function which will find a greater number among this particular list and least is another function Which will find the lowest number in this particular list?

So greatest and least values will be returned by these functions. So when I execute this here select the greatest of this one So I have 100 200 3 4 500. So when I execute this it will give you 500. That is a greatest value You can keep this number anywhere. So finally it will return only greatest value And when I execute this command this will give you the least value that is 100 so greatest and least So these functions will take some of the numbers some list of numbers we need to pass All right. So the next function these are all number functions Okay, so so far we discussed string functions and number functions now we'll move on to the date functions So these are very important to perform the date related actions So we have some date functions.

So what are those date functions? So current date and current underscore date So these are the two functions will work in the same way. So select the current date and current underscore date So this particular function will returns that current date these functions will return the current date So let's say select a current date.

So this will also return the same thing. So here month and date Similarly current underscore date also will return the same thing Year month and date. So these two commands will return the current current date present date Now we have another two commands current time So these two commands also same these two methods also same and these two methods will return the current time These two methods will return the current time only time select the current time select the current underscore time So when I execute this it will give you current time and this is also give the same thing So current date and current time we can retrieve by using these commands or these methods now when I say now It will give you date as well as time both so when I say select now So this will give you date along with the time both will give you so if you want to give or if you want to get Only date go for this one if you want to get only time Then go for this command if you want to get date as well as time then use this now command Then sis date is also there. This will also return the system time system date and in system whatever date you have configured So that butler date will be returned and here I need to use select the system select Select sis date. So this command also will give you system date.

So this is our current system date and So there are few more months. So there is one more method called month year day month is a function which will retrieve the month value from the complete date and Here is a method which will retrieve the year value from the completed and day function will retrieve the day value from the complete Complete date. So that's the use of month year and day There are a few more methods month and the next one is here and then next one is date Day the current day. So let's try to execute this over here 2009 this is a value have passed 2019 0 5 and 19 have passed so when i pass this data so this will give you 5 so what is this 5 is month so month is extracted now in the second query it will extract the year so 2019 is the year it is extracted now in the third query it is extracted the day so that is 19 is a day which is extracted so these are the different functions available in the date functions And also there are so many date functions are available. We want to refer all of them.

Just you can go through this link. So this link is provided all kinds of date functions which are available in MySQL. There are huge list guys. And we can also perform lot of actions like we can find out the number of days between two days.

And we can add the next date to the existing date. We can do minus, plus, all the operations we can perform on the date type of data. So these are all the different functions.

There is a huge list is available. So we can just go through each and every method. They will give you clear documentation how we can use those functions or methods All right. So this is all about date functions guys date functions So now I have two important queries here based on the date my query is So in the employees table, I have list of employees.

I need to filter those employees. I need to print the data of the employees Who who is joined in 1987 in theory? So I want to print the employees data whoever is joined in this particular year 1987 so I want to write a query like this.

So I want to print the data of employees Whoever is joined 1987 in the exact year So select the staff and employees will give you all the data and to filter the data We use where condition and when I say hi Did is there in the select staff and employees when I say hi, did is column is there select to start from? So select the staff and employees table we have high grade column. So if you see here high date column is there So here, where I say select a staff and employees where, what I am saying, what is my requirement is, I want to check the employees joined in the 1987 exactly in the year or not.

But in the higher date, 1987 is there. But along with this, the month and date is also there. I don't require that.

I need to compare the date with only the date part in the higher date. So how we can extract the year part from this? I need to get the year part by using year function.

So select star from employees where year of high date So this command will extract the year value from this particular date and then it will compare with the 1987 or not If this condition is true, then those records will be retrieved So when I execute this it will give you employees. We have joined exactly 1987 so now if you see the high date here all the years are 1987 okay, so this is how we need to use these functions There is another so display employees who have were joined in the June. So here I'm specifically say month name Okay month name and specify so display employees who are joined in June So this query we can write in two different ways.

So one is we can write like this. So the first query is Select a star from employees where month is a method so month of high date. So this method we discussed Because what is this method will do this method will extract the month from this date So month from this date and that month is available in the form of number Sorry in the form of a number like this.

It is a string only but into inside. This is a number So this command will return the six suppose if it is june six is written so i'm comparing with the six So this will also give you the employees. We have joined in the sixth month.

So now we can see all our six suppose I want to give the Name of the month how we can do that if you want to provide them a number of the month You can directly use month function here month of high rate is equal to 6 Suppose if you want to send name of the month then how we can compare so we have another method called month name So this will return the name of the month from this high date corner So for every date value it is extracted month name based on the value and that month name should be equal to June Now this query also will give you the same data. So all the employees have joined in the same month Okay, so this is how we need to use a date functions. It's very very important functions So now the next category is aggregate functions. So aggregate functions basically Used to perform certain calculations on multiple rows of a single column of a table multiple rows of a single column of a table these functions will return a single value so aggregate functions will always return a single value remember this aggregate functions will return always single value and these functions required multiple rows on single column so for example in my table okay so I have employees table here let's say select star from employees table so in this table I have some data so what i want to do is i want to find out the salaries like a sum of all the salaries total sum of all the salaries or i want to find out average salaries of these employees okay or i want to find out maximum salary of the employee or minimum salary of the employees what is the minimum salary of these employees then we need to go for aggregate functions because these functions works on only single column but it is considering all the rows single column All the rows so when I say max of salary column Then it will give you maximum salary when I say minimum of salary It will give you a minimum salary average and some awesome. So these are the four functions We have a BG some minimum maximum and count count will count how many number of records are there in that particular column?

So let me execute these commands. So the first function is average function suppose Want to find out average salary of these employees all employees means all the rows So I want to find out average salary of all the employees now select a BGF salary From employees and I execute this it will give you average salary of employees This is the average of salary Similarly if I want to get to some of all the salaries of employees now your salary of some of salary So here again multiple rows a single column Okay, this is the sum of all the salaries of employees now What is the minimum salary of employee and I execute this it will give you minimum salary 7 by so starting salary is 2100 Now this will give you maximum salaries of employees. So 24,000. Now if you want to use count star function, it will count how many employees are there in the table. So this will give you count.

107 records are available in the table. So these are all aggregate functions. So aggregate functions will work only on the single column. And if you notice here, everywhere I specified only single column. But also it is retaining single value.

But this needs multiple rows. So this is all about aggregate functions, so we have discussed how many functions we have discussed String functions and numeric functions and date functions then aggregate functions So string functions works on string data type numeric functions will work on numeric data types date functions will work on data Data data types and aggregation functions will work on the number of rows of single column Okay, it will return the single value so these are all built-in functions which are available in mysql so this is all about functions concept in mysql

Transcript for:Built-in Functions in MySQL

Transcript for:
Built-in Functions in MySQL