Published on

Single Row Functions - A Guide to Numeric and String Functions in SQL

Authors

Introduction

In the realm of SQL programming, single-row functions are invaluable tools that operate on individual rows or values, providing you with the power to manipulate data to your desired outcome. In this article, we will delve into two essential categories of single-row functions: Numeric Functions and String Functions. By the end of this journey, you'll have a solid grasp of these functions and how to apply them effectively in SQL queries.

Numeric Functions

POWER and POW Functions

The POWER(x, y) or POW(x, y) function is your go-to when you need to calculate the value of x raised to the power of y. For instance:

example.sql
  SELECT POW(3, 2);
--  Output: 9

ROUND Function

The ROUND(x) function rounds the argument x to the nearest integer. You can also specify the number of decimal places to round to using ROUND(x, d). Observe the following examples:

example.sql

SELECT ROUND(3878.6546);
-- Output: 3879

SELECT ROUND(3878.6546, 1);
-- Output: 3878.7

SELECT ROUND(3878.6546, 2);
-- Output: 3878.65

SELECT ROUND(3878.6546, 3);
-- Output: 3878.655

SELECT ROUND(3878.6546, -1);
-- Output: 3880

SELECT ROUND(3878.6546, -2);
-- Output: 3900

TRUNCATE Function

The TRUNCATE(x, d) function truncates the argument x to d decimal places. Let's see some examples:

example.sql

SELECT TRUNCATE(3878.6546, 0);
-- Output: 3878

SELECT TRUNCATE(3878.6546, 1);
-- Output: 3878.6

SELECT TRUNCATE(3878.6546, 2);
-- Output: 3878.65

SELECT TRUNCATE(3878.6546, 3);
-- Output: 3878.654

SELECT TRUNCATE(3878.6546, -1);
-- Output: 3870

SELECT TRUNCATE(3878.6546, -2);
-- Output: 3800

String Functions

LENGTH Function

The LENGTH(str) function returns the length of a column or a string. For instance:


SELECT LENGTH("Informatics Practices");
-- Output: 21

CONCAT Function

The CONCAT(str1, str2, ...) function concatenates multiple strings, resulting in a single string. You can pass one or more arguments to it:


SELECT CONCAT("Hello", "World", " – IP");
-- Output: HelloWorld – IP

INSTR Function

The INSTR(str, substr) function returns the position of the first occurrence of the substring substr in the string str, or 0 if not found:


SELECT INSTR("Informatics Information", "Inform");
-- Output: 1

LOWER and LCASE Functions

The INSTR(str, substr) function returns the position of the first occurrence of the substring substr in the string str, or 0 if not found:


SELECT LCASE("Informatics Practices");
-- Output: informatics practices

UPPER and UCASE Functions

Conversely, the UPPER(str) and UCASE(str) functions make the argument str uppercase:


SELECT UCASE("Informatics Practices");
-- Output: INFORMATICS PRACTICES

LEFT Function

The LEFT(str, n) function extracts the first n characters from the string str:


SELECT LEFT("I Love IP", 6);
-- Output: "I Love"

RIGHT Function

The RIGHT(str, n) function returns the last n characters from the string str:


SELECT RIGHT("I Love IP", 6);
-- Output: "ove IP"

LTRIM Function

Use the LTRIM(str) function to remove leading spaces from the left side of the string str:


SELECT LTRIM("   I     Love     IP     ");
-- Output: "I     Love     IP     "

RTRIM Function

On the other hand, the RTRIM(str) function removes trailing spaces from the right side of the string str:


SELECT RTRIM("   I     Love     IP     ");
-- Output: "   I     Love     IP"

TRIM Function

The TRIM(str) function removes both leading and trailing spaces from the string str:


SELECT TRIM("   I     Love     IP     ");
-- Output: "I     Love     IP"

SUBSTRING, SUBSTR, and MID Functions

These functions allow you to extract a portion of a string. The SUBSTRING(str, m, n), SUBSTR(str, m, n), and MID(str, m, n) functions are functionally equivalent. They return n characters starting from the mth character of the string str. If n is omitted, the function returns the rest of the string. Negative m counts from the end of the string:


SELECT SUBSTRING("I Love IP", 3, 2);
-- Output: "Lo"

SELECT SUBSTRING("I Love IP", 3);
-- Output: "Love IP"

SELECT SUBSTRING("I Love IP", -5, 2);
-- Output: "ve"

SELECT SUBSTRING("I Love IP", -5);
-- Output: "ve IP"

Date Functions

CURDATE Function

The CURDATE() function returns the current date in the 'YYYY-MM-DD' format:


SELECT CURDATE();
-- Output: "2018-01-25"

NOW and SYSDATE Functions

The NOW() function provides the current date and time in 'YYYY-MM-DD HH:MM:SS' format. It remains constant for all records during the execution of a command. In contrast, the SYSDATE() function displays the date and time at the moment of execution, offering precision:


-- NOW() output will remain the same for all records within a query
SELECT NOW();

-- SYSDATE() output will vary with each execution
SELECT SYSDATE();

DATE Function

The DATE(expr) function extracts the date part of a date or datetime expression expr:


SELECT DATE("2018-01-25 19:26:00");
-- Output: "2018-01-25"

MONTH Function

The MONTH(date) function returns the numeric month from the specified date (ranging from 0 to 12):


SELECT MONTH("2018-01-25");
-- Output: "1"

YEAR Function

The YEAR(date) function retrieves the year from the specified date (ranging from 0 to 9999):


SELECT YEAR("2018-01-25");
-- Output: "2018"

DAYNAME Function

With the DAYNAME(date) function, you can obtain the name of the weekday for a given date:


SELECT DAYNAME("2018-01-25");
-- Output: "Thursday"

DAYOFMONTH Function

The DAYOFMONTH(date) function returns the day of the month (ranging from 0 to 31):


SELECT DAYOFMONTH("2018-01-25");
-- Output: "25"

DAYOFWEEK Function

To get the day of the week as a number (1 for Sunday, 2 for Monday, and so on), use the DAYOFWEEK(date) function:


SELECT DAYOFWEEK("2018-01-25");
-- Output: "5"

DAYOFYEAR Function

Finally, the DAYOFYEAR(date) function returns the day of the year for a given date in numeric format (ranging from 1 to 366):


SELECT DAYOFYEAR("2018-02-08");
-- Output: "39"

Conclusion

Single-row functions in SQL are powerful tools that allow you to manipulate data effectively. In this article, we explored Numeric Functions, String Functions, and Date Functions, providing you with a comprehensive guide to their usage. By mastering these functions, you'll be well-equipped to enhance your SQL skills and handle a wide range of data manipulation tasks.

Feel free to explore these functions further in your SQL endeavors, and remember that practice makes perfect!

FAQs

  1. What are single-row functions in SQL?

Single-row functions in SQL operate on individual rows or values to produce output. They are essential for data manipulation and transformation.

  1. How do I use the POWER function in SQL?

The POWER function in SQL is used to calculate the value of one number raised to the power of another. For example, SELECT POWER(3, 2); returns 9.

  1. What is the purpose of the TRUNCATE function in SQL?

The TRUNCATE function in SQL is used to truncate (round down) a numeric value to a specified number of decimal places.

  1. How can I convert a string to lowercase in SQL?

You can convert a string to lowercase in SQL using functions like LCASE or LOWER. For instance, SELECT LCASE("Hello"); returns "hello."

  1. How do I extract the year from a date in SQL?

You can extract the year from a date in SQL using the YEAR function. For example, SELECT YEAR("2023-10-02"); returns 2023.

For more information on SQL and its functions, please refer to the documentation of your specific SQL database system.