Common SQL Functions: An Overview

In this tutorial, we’ll learn common functions that we can use in SQL. These functions can be a String or a Date.

Download Free Resources Here

SQL functions are used for processing or manipulating data.

Let’s first talk about the String functions. These are the SQL functions that we usually use for manipulating String characters or String values.

Table of Contents

Common SQL String Functions

LEFT and RIGHT SQL functions are for getting the left or right characters of a String. These are very common in Excel and in DAX.

To use the LEFT or RIGHT string functions, we need to specify the column name and the number of characters that will be extracted.

Here’s the result of our sample query. On the first column, we used the LEFT function so it returned the first 3 characters of the FirstName column. The 2nd column displays the last 3 characters of the FirstName because we used the RIGHT string functions.

LOWER and UPPER SQL Functions are for converting our String characters to lowercase or uppercase. The LOWER function is used for converting the string to lowercase, while the UPPER function is for converting the String to uppercase.

We can also replace characters on our String by using the REPLACE function. For this example, we’re replacing the “a” characters with “*”.

So, in our result, wherever the “a” is used, it will replace them with “*”.

The REPLICATE function is for repeating existing string or characters.

To use this function, we need to specify the character or the column that we want to repeat, and how many times we’d want it to be repeated.

Based on our sample query, this result shows that the 1st column is repeating the “A” character 3 times while the 2nd column is repeating the column value 3 times.

Next is the LEN function. This is for getting the length of a particular string or column value. For this example, we’re getting the length of the FirstName column values.

Common SQL Date Functions

One of the most commonly used Date functions is the GETDATE function. It’s for bringing in the current date.

Note that we don’t need to specify a FROM clause when using this function on a SQL command because we’re getting the result on the GETDATE function directly.

We also have the DATEPART function which returns the specified part of the date like a year, day, or month. Aside from the year, day, and month, it can also return a week number, quarter number, and many more.

For this example, we want a column to display the extracted month of the OrderDate column. So, we’ve used the DATEPART function with the MONTH function.

On our result, the 2nd column will show us the extracted month on the OrderDate column. We can also extract the year or day of the OrderDate column by just changing the MONTH to YEAR or DAY instead.

If we want to just specifically extract the day, year, or month of a specified date, then we can also use the DAY, MONTH, and YEAR functions directly without the DATEPART function.

Again, the only difference is that using the DATEPART function is more flexible as it can also return the week number, quarter number, and other related things.

Using the specific YEAR, DAY, and MONTH function can also be used with the WHERE clause to return certain records under your specified year, day, or month value.

For this example, we’d want to extract all of the data with an order date year of 2014.

The DATEADD function adds a number on a specified date part of a date. It’s for shifting the date to a particular time (1 month/day/year ago or 1 month/day/year after). So, we need to specify the date part, the number that we want to add, and the date that we want to manipulate.

For this example, we’re extracting the MONTH from the GETDATE function. We can then display the previous month or a month further by adding a negative or a positive number.

We can also use a negative number (-1).

For our example, it’ll return the previous month instead.

Again, we can use it with the MONTH, DAY, or YEAR function.

Next is the DATEDIFF function which gets the difference between two dates. For example, we can use it if we want to see the difference between the order date and ship date in days. You can change the DAY function to MONTH or YEAR if you want to get the difference in years or months instead.

For this result, the 1st column shows the difference in days between the order date and the ship date.

Other Advanced Common SQL Functions

We also have the IFF function which is also known as an “IF function” but spelled as IFF. It returns a value depending on whether the condition is TRUE or FALSE.

For this example, we want to check the value of the FirstName column and determine if its value is Catherine or not.

If the value is Catherine, the result should display “Catherine“; otherwise, it will show “Not Catherine“.

When concatenating values, they should be on the same data type.

So, concatenating the FirstName column values with the BusinessEntityID column value will return an error.

This is because the FirstName values data type is String while the BusinessEntityID is an Integer.

What we can do is use the CAST function to convert the data type of the BusinessEntityID from Integer to CHAR which is a String data type.

After using the CAST for converting the data type, we can now have this result where the FirstName values are concatenated to the BusinessEntityID values.

Similarly, we can also use the FORMAT function to format a date. For this example, we’re converting the OrderDate column to a “yyyy/MM” date format which will only display the year and month values.

Conclusion

In this post, we’ve learned the common SQL functions for manipulating and processing data. As we have discussed, there are different SQL functions for STRING, DATE, and some common advanced functions that we can use on both String, Integer, and Date values.

By learning these functions, it could make our SQL processes easier and more convenient. If you’d like to know more about this topic and other related content, you can certainly check out the list of relevant links below.

All the best,

Hafiz

Download Free Resources Here