SQL Server Functions

SQLServerFunctions

SQL Server starting with 2012 ship with a robust set of functions that can be used to make code perform faster, with fewer lines of code. The functions  can be used in ETL Process to provide better error handling. A example of this would be the Try_Parse function that allows you to check if a value can be converted.

Another example would be using the FIRST_VALUE() and LAST_VALUE() functions which work against a set of data. These functions are very useful when looking for things like month over month averages and when doing calculations. The table below contain a list of function that are supported starting with 2012 along with some examples of how to use them.

Function Type Description Syntax
IIF() Logical Returns a value based on if a Boolean expression evaluate to true or false IIF( Boolean_expression, true_value, false_value)
CHOOSE() Logical Return value at the specified index from a list CHOOSE(Index, val_1, [,val_n])
FORMAT() String Returns a formatted string FORMAT(string, format, [culture])
CONCAT() String Concatenate multiple columns into one string. CONCAT( Column1, column2, Column3)
CAST() Conversion Cast data as a different data type CAST(value as <data_type> [Length])
CONVERT() Conversion Convert data to a different data type CONVERT( <data type> [Length], value [, style] )
PARSE() Conversion Returns an expression translated to a requested data type PARSE(string as data_type [ using culture])
TRY_CAST() Conversion Check to see if a value can be successfully cast as a data type TRY_CAST ( value AS data type [length])
TRY_CONVERT() Conversion Check to see if a value can be converted to a specified data type TRY_CONVERT( <data Type> [Length], value [, style] )
TRY_PARSE() Conversion Return null if Parse fails, otherwise return translated data type that’s requested. TRY_PARSE (value AS data type [ USING culture ] )
FIRST_VALUE() Analytical Return the first value in a ordered set FIRST_VAUE([scalar_expression] )OVER ([partition_by_clause] order_by_clause [rows_range_clause]))
LAST_VALUE() Analytical Return the last value of an ordered set FIRST_VAUE([scalar_expression] )OVER ([partition_by_clause] order_by_clause [rows_range_clause]))
LAG() Analytical Return data from the row before the current row in result frame LAG (scalar_expression [,offset] [,default])

OVER ( [ partition_by_clause ] order_by_clause )

LEAD() Analytical Return data from the row after the current row in result frame LEAD (scalar_expression [,offset] [,default])

OVER ( [ partition_by_clause ] order_by_clause )

RANK() Ranking Ranks each row in a partitioned result set RANK() OVER (<Partition by clause> ORDER BY )
DENSE_RANK() Ranking Returns rank without gaps in the partitioned result set. DENSERANK() OVER (<Partition by clause> ORDER BY )
NTILE() Ranking Divide the result set into groups NTILE() OVER (<Partition by clause> ORDER BY )
ROW_NUMBER() Ranking Returns the row number for each row in a result set. ROW_NUMBER () OVER (<Partition by clause> ORDER BY )
Logical Functions
  --Basic Choose example
  Select CHOOSE( 2, 'Value1','Value2', 'Value3') as Result;
   
  --Advance Choose example
  Declare @birthday smalldatetime;
  set @birthday = '6/16/1984'
   
  select @birthday as [Birthday],CHOOSE(MONTH( @birthday) ,'Winter','Winter','Spring','Spring','Spring', 'Summer', 'Summer', 'Summer','Autumn','Autumn','Autumn','Winter') as [Birthday_Season]
   
   
  -- Basic IIF function 
  Declare @val_1 int = 1
  Declare @val_2 int = 2
select IIF( @val_1< @val_2, 'True', 'False' )
String Functions
Declare @unformatted bigint
set @unformatted = 7082362689
 
select @unformatted as UnFormatted, FORMAT(@unformatted, '###-###-####') as Formatted
 
CONCAT

Declare @string1 nvarchar(3) = 'Hel',
   @string2 nvarchar(3) = 'lo ',
   @string3 nvarchar(6) = 'World!'
 
Select CONCAT(@string1, @string2, @string3)
Conversion Functions
  --Prase()
  Select parse('Monday, 13, December 2010' as datetime using 'en-US') as [Result]
   
  --In the example below we use the prase function to prase the chinese symbol for currency
Select parse('¥669.66' AS money USING 'zh-CHS') as [Result]

  --Try_Cast
  Select 
     CASe WHEN TRY_CAST ('abc' as float) is null
        THEN 'Failed'
        ELSE 'Successful' end as [Result],
     CASE WHEN TRY_CAST( '2' as float) is null
        THEN 'Failed'
        ELSE 'Successful' end as [Results]
 
  --TRY_PARSE()
  Select 
    CASE WHEN TRY_PARSE('abc' as int using 'en-US' ) is null  
       then 'Conversion Failed'
       ELSE 'Success Parse' end as Result,
    CASE WHEN TRY_PARSE('2' as int using 'en-US')  is null
       then 'Parse Failed'
       else 'Parse Successful'
 end as Result2, PARSE('2' as int) as value

  --TRY_CONVERT()
  SELECT
    CASE WHEN TRY_CONVERT(float, 'ABC') is null 
       then 'Failed'
       ELSE 'Successful' END as [Results],
    CASE WHEN TRY_CONVERT(float, '2222.4') is null
       then 'Failed Conversion'
 ELSE 'Successful Converted' end as [Results2]

Analytical Functions

The first value function work against a ordered set in the example below the set we are working with is a table that has columns lastname, jobtitle and vacationhours. We use the first value function to get the name of the employee with the fewest number of vacation days. This is don by getting the first empolyee of a based on the partition by and order which in this case is jobtitle and vacationhours.

select
  p.LastName, e.JobTitle, e.VacationHours,
  FIRST_VALUE(p.LastName) over( partition by e.jobtitle order by e.vacationhours
              rows unbounded preceding)
from
 HumanResources.Employee e 
 inner join Person.Person p on p.BusinessEntityID =e.BusinessEntityID
 order by JobTitle

select 
SalesOrderID, CustomerID, OrderDate,
 FIRST_VALUE(OrderDate) over(partition by customerid order by orderdate asc ) as First_order,
 last_value(orderdate) over (partition by customerid order by orderdate desc rows unbounded preceding ) as RecentOrder     
from
  Sales.SalesOrderHeader SOH
  where CustomerID = 11000
 
 

These are some examples of using functions in some simple use cases.


Leave a Reply

Your email address will not be published. Required fields are marked *