Sql Server Management Studio Functions
The next section is about functions and operators. We will see the most frequently used ones. Let’s do an overview. When we discussed the WHERE clause in the earlier lectures, we’ve already discussed logical operators AND,OR, NOT, and comparison operators listed here – equal, less than, greater than and so on.
Or the next slide we will also discuss the LIKE operator. Mathematical operators – plus, minus, multiplication,division – no surprises here.For the character data types, as we discussed before, the concatenation operator is written as two vertical bars.
Concatenation combines two values into one value. And then some functions. SQL language has many functions. Here we’ll talk about the most frequently used ones. Details on the next slides.
First, the functions for the numeric data types. The ABS function returns absolute value. For the positive numbers, it returns them as they are. For negative numbers, it removes the minus sign, so they become positive. On this slide we have a SELECT without any form clause. This SELECT does not get rows from a table. It only returns one row, with the literals you provide. Next function is ROUND.
It returns the closest whole number. The number 2.8 is rounded up to 3. The number 2.3 is rounded down, to number 2. The number 2.5 is in the middle, by agreement it is rounded up, to number 3. Some more functions to get whole numbers. The TRUNC function removes the fractional part. So TRUNC from 2.8 is 2, not 3 as the ROUND would do.
And two more functions – FLOOR and CEILING.
The FLOOR returns the the whole number which is less than a given value. For positive numbers, It’s the same as TRUNC. However, for the negative numbers the results are different. In this example, removing fractional part from -2.8 results in the number -2, but the closest whole number which is less of than -2.8 is -3.
What are the Microsoft SQL database functions?
A similar thing for the CEILING function. It returns a whole number larger then you a given value. For 2.8 it’s 3. And for -2.8 it’s -2. And finally, the TRUNC can do one more thing. If you give it a second argument, it can remove only part of the fractional part, up to the given number of digits. In our example, the the value 2 in the second argument means this “keep two digits after the decimal point, remove anything else”.
So we get 2.84.All right. So we learned some functions for the numeric data types.The next topic is functions for character data type. First let’s talk about LIKE operator. This operator takes a character value, and compares it with the template. In the template, there are two special symbols: percentage, and underscore.
The first one, percentage,means any number of characters, including 0 characters. And the second one, underscore, means any character, but exactly one character.
In our example, the condition is: name like ‘percentage of percentage’. It means that we search for the rows with a name which contains the word “of” surrounded by the blanks. And the name can have any umber of characters before and after that ” of “.
In our example, we get two rows whose name matches the pattern: “Master of Puppets”, And “A Kind of Magic”. The next function is SUBSTRING. It returns as substring of a string. In the first example, we take the phrase “this is a book”, and get a substring of it, starting at position 11, with 4 characters in length.
So it gets the word “book”. In the second example, we take a substring starting at position 22. Since our string is shorter, we do not find a substring. So the result is an empty string. Notice that we did not get a NULL value in this case, we get an empty value.
The second function is POSITION. It searches for a substring inside a string. And if it finds one, then it returns the starting point – the position of a substring. In our case, the word “book” is found in the phrase “this is a book”, and it is found starting at 11th position.
If the substring is not found, as in our second example here, then the function returns 0. Again, notice that that is not NULL, it’s a zero. And finally, the CHARACTER_LENGTH. This function returns a number of characters in a given string. A few more useful functions. The function TRIM removes some characters on the left and the right of a string.
By default it removes blanks, as in the first example. But you can also provide which characters to remove, as in the second example. By default, it will remove characters both from the beginning and from the end of the string. You can also remove characters only from the beginning, or only from the end.
To do this, you can write either the LEADING or TRAILING, as shown in the third and fourth example or this slide. And finally, the functions LOWER and UPPER.
They change the given the values either to the lower case, by a LOWER function, or to the upper case by the UPPER function. All right. So we learned some functions for the character data types. Now let’s talk about some functions for date, time and timestamps. First of all, let’s convert a character data type to the date data type.
In our example, 2018-12-31 is a character value. If we want to create a date out of it, we need to provide a format – where the year, month and day are specified in the given string.
We do this by specifying the format: year, then month, then day. In the second example, the date is provided in another layout. We specify another format here, so that’s the TO_DATE function will work correctly. Same for the TIMESTAMP data type. Here we give a format for the date and for the time. For time we have abbreviations for hours, minutes and seconds.
There are also some other formatting options. Here we cover only the most frequently used ones. The next function is TO_CHAR – it’s the other way around, get a date value, and convert it to a character data type. The first argument is the date, and the second argument says how we want the string to look like. In the first example – year, then month, and then the day. The second example is day – month – year.
Notice that in the second example, the first argument is already is the date data type, and the standard literal for the data data type is provided like this: year, then month, then day. Here we take this literal, and convert it to a character data type with a given format. Sometimes you may want to get the current date, current time and current timestamp.
These functions are provided here. And finally, one more function – EXTRACT – returns different parts of the date or time. EXTRACT year from a given date – this function returns a year of that date. Same for a month and a day. The same function is applicable to time and timestamp. There you can also extract hour, minutes and seconds. All right. So we learned some useful functions and the operators for different data types.