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
Recently a student asked me how he could calculate a Moving Median. It’s not as easy as you think, as SQL doesn’t
Problem You need use SQL to calculate the Median of a result from SQL Server. Background Suppose you need to calculate the
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
Table of contentsImplicit data conversionExplicit Type Conversions with CAST AND CONVERTCASTCONVERTCONVERT DATETIME TYPESSummaryRelated SQL Conversion Function Posts Implicit data conversion Implicit conversions
The SQL LOWER function converts the input character data to a lower case. Description The LOWER function takes character expression as an
The SQL YEAR function evaluates the input date and returns the year part as an integer.Description The YEAR function works the same
The SQL CEILING function returns the smallest integer value greater than or equal to the input value. Description The CEILING function evaluates
When working with NULL, you’ll often find yourself wanting to replace these “missing” values, with something else. Just use ISNULL to do
The SQL UPPER function converts the input character expression to an upper case and returns it. Description The UPPER function reads the
The SQL FLOOR function returns the largest integer that is smaller or equal to the input expression. Description The FLOOR function helps
The SQL PATINDEX function searches an input expression for a given pattern. It returns the starting index of the first occurrence of
The SQL ISNUMERIC function checks the input expression for a valid numeric data type. It returns 1 if the input value is
The SQL RAND function generates a random number between the range of 0 and 1. The number can be greater than equal
The SQL DATEPART function returns the specified part of the input date. Description The SQL DATEPART function returns an integer value that
The SQL RTRIM function trims all the trailing spaces from the right side of the input character expression. Description The RTRIM function
The SQL LTRIM function removes the leading blank spaces from the input character expression. Description The LTRIM function returns a character expression
The SQL CHARINDEX function returns the starting position of a character expression in another character expression. Description The CHARINDEX function helps with
The SQL GETDATE function returns the current timestamp of the database as a datetime value. Description The current timestamp derives from the
The SQL ROUND function rounds a numeric value up to a specified number of decimal places or precision. Description The SQL ROUND
Use the SQL NULLIF function to compare two input expressions and returns NULL if both are equal. Description Like the COALESCE function,
Use the SQL ISNULL function to evaluate the input expression for a NULL value. If it is NULL, then ISNULL replaces it
Use the SQL CONVERT function to change an input expression from one data type into another specified data type. For example, a
SQL REPLACE function evaluates an input string for a specified substring pattern and replaces these substrings with another specified string value. Use
Use SQL DATEADD to add days, weeks, months, or any timespan specified by datepart to a date to get another. The function
The SQL DATEDIFF function calculates and returns the difference between two date values. The value returned is an integer. You can use
