SQL Single Row Functions: Math, Text and Date

Hello students, welcome to Kwickprep. Can MySQL round marks, change names to capitals, or tell you the day of your birthday? Yes, with single row functions. Today we will learn math, text and date functions, and use them inside select and where.

First, a new term. A function is a ready made tool that takes a value and gives back an answer. A single row function works on one row at a time. So if the table has five rows, you get five answers. We will study three groups, math, text and date. You can try any function with just select, without a table.

Let us start with three math functions. Power of m and n gives m raised to the power n, so power of two and three is eight. Mod of m and n gives the remainder, so mod of seventeen and five is two. Round of n and d rounds n to d decimal places, so fifteen point one nine three becomes fifteen point two.

Round has a few cases that exams love. With no second number, it rounds to a whole number, so fifteen point one nine three becomes fifteen. Round of fifteen point seven five to one place gives fifteen point eight, because five rounds up. A negative second number rounds to the left of the decimal point. So minus two rounds one thousand two hundred eighty four point five to the nearest hundred, which is one thousand three hundred.

Now text functions, which work on strings. A string is a piece of text. Ucase, or upper, changes every letter to capitals. Lcase, or lower, changes every letter to small letters. Length counts the characters, and a space also counts, so H i space five has length four.

These functions take out part of a string. Left gives n characters from the start, so K w. Right gives n characters from the end, so e p. Mid starts at position p and takes n characters, and positions start at one, so we get w i c. Mid is the same as substring. Instr gives the position where a smaller string first appears, so c is at position four, and zero means not found.

Users often type extra spaces by mistake. The text here has two spaces, then Hi, then two spaces, so six characters. Ltrim removes spaces on the left, which leaves four. Rtrim removes spaces on the right, which also leaves four. Trim removes spaces on both sides, which leaves just two characters. We use length to see the difference.

MySQL stores a date as year, month, day, with hyphens. Now gives the current date and time from the computer clock. Date takes a date and time, and keeps only the date part. Year gives the year as a number. Month gives the month number, like eight. Monthname gives its name, like August. Day and dayname follow on the next slide.

Day gives the day of the month, and dayname gives the weekday name. Let us test them on Independence Day in the year two thousand twenty six. The date goes in single quotes, as year, month, day. Monthname gives August. Day gives fifteen. Dayname gives Saturday. Pause and predict. What does month of the same date give? It gives eight.

Now let us use functions on a real table. This student table has roll, name, marks with decimals, and date of birth, called dob. For example, Riya was born on the twelfth of May, two thousand nine.

Inside select, the function runs once for every row. Ucase of name shows each name in capitals. Round of marks turns eighty eight point five into eighty nine, and sixty five point seven five into sixty six. Year of dob keeps only the birth year. The table itself does not change.

Functions also work inside where, to pick rows. Month of dob equals one finds students born in January, which is Neha. Left of name and one equals R finds names starting with R, which is Riya. With or, both students are kept.

Watch out for these four mistakes in output questions. In mid and instr, the first character is at position one, not zero. Round with minus one rounds to the nearest ten. Length counts spaces, so trim first if you want only the letters. And dates are written as year, month, day inside single quotes.

Let us revise what we learned today. A single row function gives one answer for every row. The math functions are power, round and mod. The text functions change case, count characters, take out parts, find positions and trim spaces. The date functions give the current time, the date part, and the year, month and day with their names. Use functions in select to show results, and in where to pick rows. Try each one with select in MySQL and predict the answer first.