Next Gen Data Learning – Amplify Your Skills

Blog Home

Blog

Common SQL Functions: An Overview

by | Power BI, SQL for Power BI

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

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.

Common SQL Functions

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.

Common SQL Functions

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

Common SQL Functions

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.

Common SQL Functions

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

Common SQL Functions

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

The REPLICATE function is for repeating existing string or characters.

Common SQL Functions

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.

Common SQL Functions

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 Functions

Common SQL Date Functions

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

Common SQL Functions
Common SQL Functions

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.

Common SQL Functions

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.

Common SQL Functions

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.

Common SQL Functions

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

Common SQL Functions

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.

Common SQL Functions

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

Common SQL Functions

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

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

Common SQL Functions

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.

Common SQL Functions

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.

Common SQL Functions

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

Common SQL Functions

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.

Common SQL Functions

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.

Common SQL Functions

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.

Common SQL Functions

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

***** Related Links *****
SQL Server Download And Installation
SQL SELECT Statement: An Introduction

WHERE Clause For Filtering Data

***** Related Course Modules *****
DAX Studio For Power BI And SSAS – Beginner To Advanced

SQL For Power BI Users
DAX for SQL Developers

***** Related Support Forum Posts *****
How To Use Getdate Function In SQL Query
Query Folding disables With Split Column Power Query Function
Difference between MAX and LastDate
For more SQL functions queries to review see here….

Related Posts