SQL Functions
Built-In Functions
You can use SQL functions to enhance and extend SQL. You’ll incorporate Functions into expressions to create different results. For instance, you can use the function LOWER to change a column’s values to all lower case.
There are many different functions. Some are useful called as they are, such as LOWER, others come into their own when used in combination with other functions.
That can make functions somewhat tricky to use, but also fun.
You’ll find the community categorizes SQL functions to topic. For instance, there are functions dealing with dates and times.
Check out these popular categories to master functions:
If you want to read more about built-in functions, Read the article Introduction to SQL Servers Built in Functions
Latest Posts
-
Concatenate Text with SQL and how to protect from NULL.
In this article I’ll show you three ways you can use SQL to concatenate columns into a single expression. You’ll see as we go through this exercise that CONCAT_WS comes out on top for handling NULL the best! The examples are based on the PizzaDB, git it here! The goal is to combine the FirstName…
-
Calculate Moving Median in SQL
Recently a student asked me how he could calculate a Moving Median. It’s not as easy as you think, as SQL doesn’t provide a built-in MEDIAN() function, nor an easy way calculate the “moving” aspect. In this article we’ll calculate a Moving median using SQL Server. Let’s use the PizzaDB sample database for example data.…
-
Use SQL to Find the MEDIAN
Problem You need use SQL to calculate the Median of a result from SQL Server. Background Suppose you need to calculate the Median using SQL. You can loosely define the median as the “middle” value of your data set. If you were calculating the median by hand, you would use the following rules to do…
-
SQL SUM Function
The SQL SUM function returns the total value within a table or group. In its simplest use, SUM() totals a column for all results and returns a single value. In this example the query returns the total orders for all SalesOrderDetail records SELECT SUM(LineTotal) AS OrderTotal FROM Sales.SalesOrderDetail You can also filter your results prior to totaling. Here the SUM() is restricted to two…
-
SQL Data Conversions
Implicit data conversion Implicit conversions are those conversions that occur automatically whenever the CAST or CONVERT functions aren’t used. Not all values can be implicitly converted to another data type. The following chart shows what can be implicitly converted for the common data types we’ve previously covered: Please keep in mind this chart shows what…
-
SQL LOWER Function (Transact SQL)
The SQL LOWER function converts the input character data to a lower case. Description The LOWER function takes character expression as an input and converts it into a lower case text. It helps to perform a case-insensitive search in the database. The SQL search is case-sensitive. The value Lower and lower are different. The Lower…
-
SQL YEAR Function (Transact SQL)
The SQL YEAR function evaluates the input date and returns the year part as an integer.Description The YEAR function works the same as the DATEPART (year, date) and returns the year part of the specified date. SQL YEAR Usage Notes The only argument for the YEAR function is the date. It can be a database…
-
SQL CEILING Function (Transact SQL)
The SQL CEILING function returns the smallest integer value greater than or equal to the input value. Description The CEILING function evaluates the right side of the decimal value and returns an integer value that is least greater than or equal to the input value. CEILING is another SQL function for approximating numerical values like…
-
Replace Missing Values in your Queries using ISNULL
When working with NULL, you’ll often find yourself wanting to replace these “missing” values, with something else. Just use ISNULL to do so. In this video I’ll walk through some of the example and you’ll see it is pretty easy to use. If you like what you are seeing, then why not get some super…
-
SQL UPPER Function (Transact SQL)
The SQL UPPER function converts the input character expression to an upper case and returns it. Description The UPPER function reads the input expression and converts lowercase characters to uppercase. SQL UPPER Function Usage Notes The character expression is the only argument for the UPPER function. It is the text to be converted to an…
-
SQL FLOOR Function (Transact SQL)
The SQL FLOOR function returns the largest integer that is smaller or equal to the input expression. Description The FLOOR function helps for approximating numeric values. It rounds the numeric expression to the value that is the largest number less than or equal to the input number. SQL FLOOR Usage Notes The FLOOR function takes…
-
SQL PATINDEX Function (Transact SQL)
The SQL PATINDEX function searches an input expression for a given pattern. It returns the starting index of the first occurrence of the pattern found in the input expression. Description The PATINDEX function is very similar to the LIKE function in SQL. However, it returns the starting position of the first occurrence of the pattern…
-
SQL ISNUMERIC Function (Transact SQL)
The SQL ISNUMERIC function checks the input expression for a valid numeric data type. It returns 1 if the input value is numeric and 0 if it is not. Description The ISNUMERIC function in SQL tells if the input expression can be converted to a numeric value or not. It returns an integer as output…
-
SQL RAND Function (Transact SQL)
The SQL RAND function generates a random number between the range of 0 and 1. The number can be greater than equal to zero but less than one. Description The RAND function gives a completely random number if no seed value is specified. The RAND function produces a repetitive random number for all the successive…
-
SQL DATEPART Function (Transact SQL)
The SQL DATEPART function returns the specified part of the input date. Description The SQL DATEPART function returns an integer value that indicates the part of the date specified by the user. The interval to be retrieved can be a date, year, hour, minute, etc. Returns an integer which represents the specified part of a…
-
SQL RTRIM Function (Transact SQL)
The SQL RTRIM function trims all the trailing spaces from the right side of the input character expression. Description The RTRIM function returns a character expression after removing all the blank spaces from the right side. SQL RTRIM Usage Notes The only argument to the RTRIM function is the character expression that is to be…
-
SQL LTRIM Function (Transact SQL)
The SQL LTRIM function removes the leading blank spaces from the input character expression. Description The LTRIM function returns a character expression after removing all the blank spaces from the left side of the input expression. SQL LTRIM Usage Notes The input expression should be explicitly convertible to a varchar value. It can be a…
-
SQL CHARINDEX Function (Transact SQL)
The SQL CHARINDEX function returns the starting position of a character expression in another character expression. Description The CHARINDEX function helps with string data manipulation like some other SQL functions of SUBSTRING, REPLACE, CONCAT, etc. The CHARINDEX function finds the beginning of a character expression in a given character expression. The result of the CHARINDEX…
-
SQL GETDATE Function (Transact SQL)
The SQL GETDATE function returns the current timestamp of the database as a datetime value. Description The current timestamp derives from the operating system on which the SQL server is running. The GetDATE function returns the timestamp in the format YYYY-MM-DD hh:mm:ss:mmm. SQL GETDATE Usage Notes The return data type of the GETDATE function is…
-
SQL ROUND Function (Transact SQL)
The SQL ROUND function rounds a numeric value up to a specified number of decimal places or precision. Description The SQL ROUND function helps to handle numeric data according to the requirements. Some other SQL functions for this purpose are CEILING, FLOOR, etc. If we do not want to display complete numeric values, we can…
-
SQL NULLIF Function (Transact SQL)
Use the SQL NULLIF function to compare two input expressions and returns NULL if both are equal. Description Like the COALESCE function, the NULLIF function is a control flow function in SQL. It checks if the specified expressions are equal. It is a simpler version of a CASE expression with the condition comparing two input…
-
SQL ISNULL Function (Transact SQL)
Use the SQL ISNULL function to evaluate the input expression for a NULL value. If it is NULL, then ISNULL replaces it with another specified value. Description The ISNULL function helps to deal with the NULL values in database records. If we want to view a column with NULL values, we can handle them using…
-
SQL CONVERT Function (Transact SQL)
Use the SQL CONVERT function to change an input expression from one data type into another specified data type. For example, a decimal value to an integer or a date value to a string. Description The SQL CONVERT function is one of the SQL functions to change the data type of a variable or an…
-
SQL REPLACE Function (Transact SQL)
SQL REPLACE function evaluates an input string for a specified substring pattern and replaces these substrings with another specified string value. Use it to substitute one set of characters for another with a character based value. Description The REPLACE function’s purpose is string manipulation. It replaces string patterns within a string with a new string.…
-
SQL DATEADD Function (Transact SQL)
Use SQL DATEADD to add days, weeks, months, or any timespan specified by datepart to a date to get another. The function returns the modified date. Description The function adds or deletes a specified time period from the date value. The period value can be a year, month, day, week, hour, minute, second, etc. A…
-
SQL DATEDIFF Function (Transact SQL)
The SQL DATEDIFF function calculates and returns the difference between two date values. The value returned is an integer. You can use DATEDIFF to calculate a wide variety of calendar calculation by varying the datepart parameter. Description Use SQL DATEDIFF to return the difference between the two dates based on a specified date part. The…
-
SQL CAST Function (Transact SQL)
The SQL CAST function converts an input expression of one data type into another data type. Description Data types of values often convert to meet different format requirements in SQL queries. Sometimes these are implicit conversions. For example, while multiplying a decimal value with an integer value, the SQL engine first converts the integer value…
-
SQL SUBSTRING Function (Transact SQL)
Description The SQL SUBSTRING function extracts a part of input expression or a ‘substring’ and returns it. The starting position and the substring’s length are passed as arguments of the SUBSTRING function. The SUBSTRING function is used to return characters from within another string. The general form of the SUBSTRING function is SUBSTRING(value, position, length)…
-
SQL STUFF Function (Transact SQL)
Description The SQL STUFF function deletes a specified number of characters from a character expression and replaces them with another substring. In other words, it inserts a string into another string. The new string value, position to insert the new string, and the length of the new string are passed as arguments in the function.…
-
SQL RIGHT Function (Transact SQL)
Description The SQL RIGHT function returns the specified number of rightmost characters of a given character expression. The rightmost characters are those at the “end” of the string. SQL RIGHT Usage Notes The character expression as the first argument of the RIGHT function can be a constant string, a variable string, or a database column.…
-
SQL LEFT Function (Transact SQL)
Description The SQL LEFT() function returns the specified number of characters starting from the left side of a given character expression. LEFT The LEFT function is used to return either the beginning or ending portion of a string. LEFT will return the beginning characters; whereas, RIGHT is used to return the ending characters. The general…
-
SQL LEN (Transact SQL)
The SQL LEN function returns the number of characters within a string. Keep in mind the count of characters returned does not include training spaces. Description The SQL LEN function return the length of a string (number of characters). It does not include trailing spaces. The LEN function returns the number of characters in a…
-
SQL COUNT Function
The SQL COUNT function is an aggregate function used to count rows. Use it alone within a SELECT statement to return a count of all rows within a table, or with a GROUP BY to provide a count of rows within each group. Use COUNT(*) to count every record in the grouping or COUNT(expression) to count every record where expression’s result isn’t NULL. Use DISTINCT with…
-
SQL APPROX_COUNT_DISTINCT Function
Use the Approximate Count Distinct function, APPROX_COUNT_DISTINCT, to return an estimate distinct count of values withing rows. It is best suited for very large tables where performance matters over precision. The latest 2019 version of SQL Server introduced many functions to the system and enriched the database engine in order to make it work faster…
-
SQL MIN Function
The SQL MIN function returns the smallest value within a table or group. Throughout this section we’ll use the HumanResource.Employee table for our examples: Using the data above, MIN calculates the smallest SickLeaveHours amount for the entire table: When used with GROUP BY, MIN returns the largest value within a group. Here is a similar query showing the minimum SickLeaveHours by JobTitle: Additional SQL MIN Resources …
-
SQL MAX Function
The SQL MAX function returns the largest value within a table or group. In this section, we’ll use the HumanResource.Employee table for our examples: Using the data above, MAX calculates the largest SickLeaveHours amount for the entire table: When used with GROUP BY, MAX returns the largest value within a group. You can see this in this query showing the maximum SickLeaveHours by JobTitle: Additional SQL MAX Resources To learn…
-
SQL AVG Function
The SQL AVG function returns the average value within a table or group. In this section, we’ll use the HumanResource.Employee table for our examples: Using sample data above, AVG calculates the average SickLeaveHours amount for the entire table: When used with GROUP BY, AVG returns the average value within a group. You can see this in this query showing the maximum SickLeaveHours by JobTitle: Additional SQL AVG Resources To learn more,…
-
SQL COALESCE Function and NULL
Knowing when to use the SQL COALESCE function is a lifesaver when you’re dealing with NULL. As you know, NULL is a tricky concept, and it seem what ever NULL “touches” in an expression, it renders the result NULL. So, when you’re dealing with NULL, how can you break out of the cycle? That is,…
-
Can I have a CASE Statement in the WHERE Clause?
I’m commonly asked whether whether I can have a CASE Statement in the WHERE Clause. There are a number of examples using the CASE WHEN construct in SQL, such as the SELECT columns or in ORDER BY clauses, but we tend to forget CASE can be used wherever an expression is expected. Where Can I…
-
How to use the Choose Function with Select
In this video we’ll walk you though how to use the CHOOSE function with SELECT. You’ll see how the function works and then a practical example using some sample data. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about SQL Server! Once you have…
-
Calculate the Last Day of the Month using SQL
When working with SQL dates, sometimes you need to calculate the end of the month. Months are tricky! Some are 28 days, others 30 or 31, and now and then there’s a leap year! So, given a date, how do you calculate the number of days remaining in the month? The calculation is really a…
-
Introduction to SQL Server’s Built-In Logical Functions
Logical functions provide a way to use logical conditions to display one of several values. You can use logical functions to test a field’s value such as gender (M or F) and display another value(‘Male’ or ‘Female’) as a result.In this article we describe how to use the CHOOSE and IIF functions. CHOOSE is really…
-
Built-In Functions (SQL Server)
You can use Built-In functions in SQL SELECT expressions to calculate values and manipulate data. Use these SQL functions anywhere expressions are allowed. Common uses of functions include changing a name to all upper case. In this article, we’ll introduce you to basic concepts to use SQL Function in SQL Server, MySQL, PostgreSQL, and Oracle.…
-
SQL CASE Statement – Simple and Searched Forms
The main purpose of a SQL CASE expression returns a value based on one or more conditional tests. Use CASE expressions anywhere in a SQL statement expression is allowed. Though truly an expression, some people refer to them as “CASE statements.” This most likely stems from their use in programming languages. The SQL CASE expression…
