Functions in SQL Server

SQL Server built-in functions are either deterministic or nondeterministic. Functions are deterministic when they always return the same result any time they are called by using a specific set of input values. Functions are nondeterministic when they could return different results every time they are called, even with the same specific set of input values.
 
Functions that take a character string input and return a character string output use the collation of the input string for the output. Functions that take no character inputs and return a character string use the default collation of the current database for the output. Functions that take multiple character string inputs and return a character string use the rules of collation precedence to set the collation of the output string.
 
In this article, I am going to explain about some SQL Server 2005 functions with examples. A function performs an operation and returns a value.  A function consists of the function name, followed by a set of parenthesis that contains any parameter or arguments required by the function. If a function requires two or more arguments you can separate them with commas.
 
Here are going to discuss about some string functions, numeric functions and date/time functions.
 
Note - I am using my own database table for examples. See database table in figure 1.
 
 
Figure 1.
 

String Functions in SQL Server

 
1. LEN (string) - Returns the number of characters of the specified string expression, excluding trailing blanks.
  1. use Vendor  
  2. GO  
  3. Select LEN('Raj'), LEN('Raj    'FROM VENDOR WHERE VendorFName='Raj'  
  4. GO  
LEN doesn't count length of spaces. So result looks like this.
 
LEN function in SQL Server
 
2. LTRIM (string) - LTRIM function to return a character expression after removing leading spaces.
  1. use Vendor  
  2. GO  
  3. use Vendor  
  4. SELECT LTRIM('   Raj')  
  5. FROM VENDOR WHERE VendorFName='Raj'  
  6. Go 
LTRIM  function in SQL Server
 
3. RTRIM (string) - RTRIM function to return a character expression after removing trailing spaces.
  1. use Vendor  
  2. GO  
  3. use Vendor  
  4. Select RTRIM('Raj     ')  
  5. FROM VENDOR WHERE VendorFName='Raj'  
  6. GO 
RTRIM function in SQL Server
 
4. LEFT (string, length) - Returns the specified number of characters from the beginning of the string.
  1. use Vendor  
  2. SELECT VendorFName, VendorLName, LEFT(VendorFName, 1) + LEFT (VendorLName, 1) AS Initials FROM Vendor 
LEFT function in SQL Server
 
5. RIGHT (string, length) - Returns the specified number of characters from the end of the string.
  1. use Vendor  
  2. SELECT VendorFName, VendorLName, RIGHT(VendorFName, 1) + RIGHT (VendorLName, 1) AS Initials FROM Vendor 
Right function in SQL Server
 
6. SUBSTRING (string, start, length) - Returns the specified number of characters from the string starting at the specified position.
  1. use Vendor  
  2. GO  
  3. SELECT SUBSTRING('beniwal', 2, 2) FROM VENDOR WHERE VendorFName='Raj'  
  4. GO
SUBSTRING function in SQL Server
 
7. REPLACE (search, find, replace) - Returns the search string with all occurrences of the find string replaced with the replace string.
  1. use Vendor  
  2. GO  
  3. use Vendor  
  4. SELECT REPLACE('Beniwal''Beniwal''Choudhary')  
  5. FROM VENDOR WHERE VendorFName='Raj'  
  6. GO  
REPLACE function in SQL Server
 
8. REVERSE (string) - Returns the string with the character in reverse order.
  1. use Vendor  
  2. GO  
  3. use Vendor  
  4. SELECT REVERSE('Raj')  
  5. FROM VENDOR WHERE VendorFName='Raj'  
  6. GO 
Reverse function in SQL Server
 
9. CHARINDEX (find, search [, start]) - Returns an integer that represents the position of the first occurrence of the find string in the search string starting at the specified position. If the starting position isn't specified, the search starts at the beginning of the string. If the staring isn't found, the functions returns zero.
  1. use Vendor  
  2. GO  
  3. use Vendor  
  4. SELECT CHARINDEX('w''Beniwal')  
  5. FROM VENDOR WHERE VendorFName='Raj'  
  6. GO 
CHARINDEX function in SQL Server
 
10. PATINDEX (find, search [, start]) - PATINDEX is useful with text data types; it can be used in a WHERE clause in addition to IS NULL, IS NOT NULL, and LIKE (the only other comparisons that are valid on text in a WHERE clause). If either pattern or expression is NULL, PATINDEX returns NULL when the database compatibility level is 70. If the database compatibility level is 65 or earlier, PATINDEX returns NULL only when both pattern and expression are NULL.
  1. use Vendor  
  2. GO  
  3. use Vendor  
  4. SELECT PATINDEX('%Krew%', VendorLName)  
  5. FROM VENDOR WHERE VendorId=5  
  6. GO 
Patindex function in SQL Server
 
11. LOWER (string) - Returns the string converted to lowercase letters.
  1. use Vendor  
  2. GO  
  3. use Vendor  
  4. SELECT LOWER('Raj')  
  5. FROM VENDOR WHERE VendorFName='Raj'  
  6. GO  
Lower function in SQL Server
 
12. UPPER (string) - Returns the string converted to uppercase letters.
  1. use Vendor  
  2. GO  
  3. use Vendor  
  4. SELECT UPPER('Raj')  
  5. FROM VENDOR WHERE VendorFName='Raj'  
  6. GO 
Upper function in SQL Server
 
13. SPACE (integer) - Returns the string with the specified number of space characters (blanks).
  1. use Vendor  
  2. GO  
  3. use Vendor  
  4. SELECT VendorFName + ',' + SPACE(2) + VendorLName  
  5. FROM VENDOR WHERE VendorFName='Raj'  
  6. GO  
Space function in SQL Server
 

Numeric Functions in SQL Server

 
1. ROUND (number, length, [function]) - Returns the number rounded to the precision specified by length. If length is positive, the digits to the right of the decimal point are rounded. If it's negative the digits to the left of the decimal point are rounded. To truncate the number rather than round it code a non zero value for function.
  1. USE Vendor  
  2. GO  
  3.   
  4. --Used Round the estimates  
  5. SELECT ROUND(123.9994, 3), ROUND(123.9995, 3)  
  6.   
  7. --Use ROUND and rounding approximations  
  8. SELECT ROUND(123.4545, 2), ROUND(123.45, -2)  
  9.   
  10. --Use ROUND to truncate  
  11. SELECT ROUND(150.75, 0), ROUND(150.75, 0, 1)  
  12.   
  13. GO 
Round function in SQL Server
 
2. ISNUMERIC(expressions) - Returns a value of 1 (true) if the expression is a numeric value; returns a values of 0 (false) otherwise.
  1. USE Vendor  
  2. GO  
  3. SELECT IsNumeric(VendorId) FROM Vendor  
  4. SELECT ISNumeric(VendorFName) FROM Vendor  
  5. GO  
Isnumeric function in SQL Server
 
3. ABS (number) - Returns the absolute value of number.
  1. USE Vendor  
  2. GO  
  3. SELECT ABS(-1.0), ABS(0.0), ABS(1.0)  
  4. GO
ABS function in SQL Server
 
4. CEILING (number) - Returns the smallest integer that is greater than or equal to the number.
  1. USE Vendor  
  2. GO  
  3. SELECT CEILING($123.45),CEILING($-123.45), CEILING($0.0)  
  4. GO 
Ceiling function in SQL Server
 
5. FLOOR (number) - Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
  1. USE Vendor  
  2. GO  
  3. SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)  
  4. GO 
Floor function in SQL Server
 
6. SQUARE (float_number) - Returns the square of the given expression.
  1. USE Vendor  
  2. GO  
  3. DECLARE @h float, @r float  
  4. SET @h = 5  
  5. SET @r = 1  
  6. SELECT PI()* SQUARE(@r)* @h AS 'Cyl Vol'  
  7. GO  
Square function in SQL Server
 
7. SQRT (float_number) - Returns a square root of a floating-point number.
  1. USE Vendor  
  2. GO  
  3. DECLARE @myvalue float  
  4. SET @myvalue = 1.00  
  5. WHILE @myvalue < 10.00  
  6.    BEGIN  
  7.       SELECT SQRT(@myvalue)  
  8.       SELECT @myvalue = @myvalue + 1  
  9.    END  
  10. GO
SQRT function in SQL Server
 
8. RAND ([seed]) - Returns a random float value from 0 through 1.
 
Is an integer expression (tinyint, smallint, or int) that specifies the seed value. If seed is not specified, Microsoft SQL Server 2000 assigns a seed value at random. For a given seed value, the result returned is always the same.
  1. USE Vendor  
  2. GO  
  3. DECLARE @counter smallint  
  4. SET @counter = 1  
  5. WHILE @counter < 5  
  6.    BEGIN  
  7.       SELECT RAND() Random_Number  
  8.       SET NOCOUNT ON  
  9.       SET @counter = @counter + 1  
  10.       SET NOCOUNT OFF  
  11.    END  
  12. GO  
RAND function in SQL Server
 

Date/Time Functions in SQL Server

 
1. GetDate () - Returns the current system date and time in the Microsoft SQL Server standard internal format for date time values.
  1. USE Vendor  
  2. GO  
  3. SELECT GetDate()  
  4. GO 
GetDate function in SQL Server
 
2. GETUTCDATE() - Returns the current UTC date and time based on the system's clock and time zone setting. UTC (Universal Time Coordination) is the same as Greenwich Mean Time.
  1. USE Vendor  
  2. GO  
  3. SELECT GETUTCDATE()  
  4. GO 
GETUTCDATE function in SQL Server
 
3. DAY (date) - Returns the day of the month as an integer.
  1. USE Vendor  
  2. GO  
  3. SELECT DAY('03/12/1998'AS 'Day Number'  
  4. GO  
DAY function in SQL Server
 
4. MONTH (date) - Returns the month as an integer.
  1. USE Vendor  
  2. GO  
  3. SELECT "Month Number" = MONTH('03/12/1998')  
  4. SELECT MONTH(0), DAY(0), YEAR(0)  
  5. GO 
MONTH function in SQL Server
 
5. YEAR (date) - Returns the 4-digit year as an integer.
  1. USE Vendor  
  2. GO  
  3. SELECT "Year Number" = YEAR('03/12/1998')  
  4. GO 
YEAR function in SQL Server
 
6. DATENAME (datepart, date) - Returns an integer representing the specified date part of the specified date.
  1. USE Vendor  
  2. GO  
  3. SELECT DATENAME(month, getdate()) AS 'Month Name'  
  4. GO 
DATENAME function in SQL Server
 
7. DATEPART(datepart, date)
 
Is the parameter that specifies the part of the date to return? The table lists date parts and abbreviations recognized by Microsoft SQL Server.
 
Datepart
Abbreviations
year
yy, yyyy
quarter
qq, q
month
mm, m
dayofyear
dy, y
day
dd, d
week
wk, ww
weekday
dw
hour
hh
minute
mi, n
second
ss, s
millisecond
ms
 
The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.
 
The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.
 
Date
 
Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.
 
If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.
 
For more information about specifying time values, see Time Formats. For more information about specifying dates, see datetime and smalldatetime.
  1. USE Vendor  
  2. GO  
  3. SELECT GETDATE() AS 'Current Date'  
  4. SELECT DATEPART(month, GETDATE()) AS 'Month Number'  
  5. SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)  
  6. GO  
DATEPART function in SQL Server
 
8. DATEADD (datepart, number, date) - Returns the date that results from adding the specified number of datepart units to the date.
  1. USE Vendor  
  2. GO  
  3. SELECT DATEADD(day, 21, PostedDate) AS timeframe FROM Vendor  
  4. GO 
DATEADD function in SQL Server
 
9. DATEDIFF (datepart, startdate, enddate) - Returns the number of datepart units between the specified start date and end date.
  1. USE Vendor  
  2. GO  
  3. SELECT DATEDIFF(day, posteddate, getdate()) AS no_of_days  
  4. FROM Vendor WHERE VendorFName='Raj'  
  5. GO 
DATEDIFF function in SQL Server
 
10. ISDATE (expression) - Returns a value of 1(true) if the expression is a valid date/time value; returns a value of 0(false) otherwise.
  1. USE Vendor  
  2. GO  
  3. DECLARE @datestring varchar(8)  
  4. SET @datestring = '12/21/98'  
  5. SELECT ISDATE(@datestring)  
  6. GO  
ISDATE function in SQL Server
 
More Functions -
 
1. CASE - Evaluate a list of conditions and returns one of multiple possible return expressions.
 
CASE has two formats:
  • The simple CASE function compares an expression to a set of simple expressions to determine the result.
  • The searched case function evaluates a set of boolean expressions to determine the result.
Syntax
 
Simple CASE function
 
CASE input_expression
          WHEN when_expression THEN result_expression
                   [...n]
          [
                   ELSE else_result_expression
          ]
END
 
Searched CASE function
 
CASE
          WHEN Boolean_expression THEN result_expression
                   [...n]
          [
          ELSE else_result_expression
          ]
END
  1. use Vendor  
  2. GO  
  3. SELECT VendorId, VendorFName, VendorLName,  
  4. CASE VendorId  
  5.           WHEN 1 THEN 'This is vendor id one'  
  6.           WHEN 2 THEN 'This is vendor id two'  
  7.           WHEN 3 THEN 'This is vendor id three'  
  8.           WHEN 4 THEN 'This is vendor id four'  
  9.           WHEN 5 THEN 'this is vendor id five'  
  10. END AS PrintMessage  
  11. FROM Vendor 
Case function in SQL Server
 
2. COALESCE - Returns the first nonnull expression among its arguments.
 
Syntax
 
COALESCE (expression [...n])
  1. use Vendor  
  2. GO  
  3. SELECT PostedDate, COALESCE(PostedDate, '1900-01-01'AS NewDate  
  4. FROM Vendor  
COALESCE function in SQL Server
 
3. ISNULL - Replaces NULL with the specified replacement value.
 
Syntax
 
ISNULL (check_expression, replacement_value)
  1. use Vendor  
  2. GO  
  3. SELECT PostedDate, ISNULL(PostedDate, '1900-01-01'AS NewDate  
  4. FROM Vendor  
  5. GO
ISNULL Function in SQL Server
 
4. GROUPING - Is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator or 0 when the row is not the result of CUBE or ROLLUP. Grouping is allowed only in the select list associated with a GROUP BY clause that contains either the CUBE or ROLLUP operator.
 
Syntax:  GROUPING (column_name)
  1. Use Vendor  
  2. GO  
  3. SELECT royality, SUM(advance) 'total advance'GROUPING(royality) 'grp'  
  4. FROM advance  
  5. GROUP BY royality  
  6. WITH ROLLUP  
Grouping in SQL Server
 
5. ROW_Number() - Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition
 
Syntax:  ROW_NUMBER ( )     OVER ([<partition_by_clause>] <order_by_clause>)
 
Note:  The ORDER BY in the OVER clause orders ROW_NUMBER. If you add an ORDER BY clause to the SELECT statement that orders by a column(s) other than 'Row Number' the result set will be ordered by the outer ORDER BY.
  1. Use Vendor  
  2. GO  
  3. SELECT VendorFName, VendorLName,  
  4. ROW_Number() Over(ORDER BY PostedDate) AS 'Row Number'  
  5. FROM Vendor  
  6. GO
ROW_Number in SQL Server
 
6. RANK () - Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
 
Syntax: RANK ( )    OVER ([< partition_by_clause >] < order_by_clause >)
 
Arguments: < partition_by_clause >
 
Divides the result set produced by the FROM clause into partitions to which the RANK function is applied. For the syntax of PARTITION BY, see OVER Clause (Transact-SQL).
 
< order_by_clause >
 
Determines the order in which the RANK values are applied to the rows in a partition. For more information, see ORDER BY Clause (Transact-SQL). An integer cannot represent a column when the < order_by_clause > is used in a ranking function.
  1. Use Vendor  
  2. GO  
  3. SELECT VendorId, VendorFName, VendorLName,  
  4. RANK() Over(PARTITION BY PostedDate ORDER BY VendorId) AS 'RANK'  
  5. FROM Vendor ORDER BY PostedDate DESC  
  6. GO  
Rank in SQL Server
 
7. DENSE_RANK () - Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
 
Syntax:  DENSE_RANK ( )    OVER ([< partition_by_clause >] < order_by_clause >)
 
Arguments: < partition_by_clause >
 
Divides the result set produced by the FROM clause into partitions to which the DENSE_RANK function is applied. For the syntax of PARTITION BY, see OVER Clause (Transact-SQL).
 
< order_by_clause >
 
Determines the order in which the DENSE_RANK values are applied to the rows in a partition. An integer cannot represent a column in the <order_by_clause> that is used in a ranking function.
  1. Use Vendor  
  2. GO  
  3. SELECT VendorId, VendorFName, VendorLName,  
  4. DENSE_RANK() OVER(PARTITION BY PostedDate ORDER BY VendorId) AS 'DENSE RANK'  
  5. FROM Vendor ORDER BY PostedDate DESC  
  6. GO 
DENSE_RANK in SQL Server
 
NTILE (integer_expression) - Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
 
Syntax: NTILE (integer_expression) OVER ([<partition_by_clause>] < order_by_clause >)
 
Arguments: integer_expression
 
Is a positive integer constant expression that specifies the number of groups into which each partition must be divided? integer_expression can be of type int, or bigint.
 
Note: integer_expression can only reference columns in the PARTITION BY clause. integer_expression cannot reference columns listed in the current FROM clause.
 
<partition_by_clause>
 
Divides the result set produced by the FROM clause into partitions to which the RANK function is applied. For the syntax of PARTITION BY, see OVER Clause (Transact-SQL).
 
< order_by_clause >
 
Determines the order in which the NTILE values are assigned to the rows in a partition. For more information, see ORDER BY Clause (Transact-SQL). An integer cannot represent a column when the <order_by_clause> is used in a ranking function.
  1. Use Vendor  
  2. GO  
  3. SELECT VendorFName, VendorLName,  
  4. NTILE(4) OVER(PARTITION BY PostedDate ORDER BY VendorId DESCAS 'Quartile'  
  5. FROM Vendor  
  6. GO 
NTILE in SQL Server