SQLServerCh09

Chapter 9 How to use functions Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 1 Objectives Applied  Code queries that require any of the functions presented in this chapter for working with string, numeric, and date/time data.  Code queries that use any of the general purpose functions presented in this chapter. Knowledge  Describe how the use of functions can solve the problems associated with (1) sorting string data that contains numeric values, and (2) doing date or time searches. Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 2 Some of the string functions LEN(string) LTRIM(string) RTRIM(string) LEFT(string,length) RIGHT(string,length) SUBSTRING(string,start,length) REPLACE(search,find,replace) REVERSE(string) CHARINDEX(find,search[,start]) PATINDEX(find,search) CONCAT(value1,value2[,value3]... LOWER(string) UPPER(string) SPACE(integer) Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 3 String function examples Function Result LEN('SQL Server') LEN(' SQL Server ') LEFT('SQL Server', 3) LTRIM(' SQL Server ') RTRIM(' SQL Server ') LTRIM(RTRIM(' SQL Server ')) LOWER('SQL Server') UPPER('ca') PATINDEX('%v_r%', 'SQL Server') CHARINDEX('SQL', ' SQL Server') CHARINDEX('-', '(559) 555-1212') SUBSTRING('(559) 555-1212', 7, 8) REPLACE(RIGHT('(559) 555-1212', 13), 10 12 'SQL' 'SQL Server ' ' SQL Server' 'SQL Server' 'sql server' CA 8 3 10 555-1212 ') ', '-') 559-555-1212 CONCAT('Run time: ',1.52,' seconds') Run time: 1.52 seconds Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 4 A SELECT statement that uses three functions Select VendorName, VendorContactLName + ', ' + LEFT(VendorContactFName, 1) + '.' AS ContactName, RIGHT(VendorPhone, 8) AS Phone FROM Vendors WHERE SUBSTRING(VendorPhone, 2, 3) = 559 ORDER BY VendorName; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 5 How to sort by a string column that contains numbers Sorted by the ID column SELECT * FROM StringSample ORDER BY ID; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 6 How to sort by a string column that contains numbers (continued) Sorted by the ID column cast to an integer SELECT * FROM StringSample ORDER BY CAST(ID AS int); Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 7 How to use the string functions to parse a string SELECT Name, LEFT(Name, CHARINDEX(' ', Name) - 1) AS First, RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name) ) AS Last FROM StringSample; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 8 Some of the numeric functions ROUND(number,length[,function]) ISNUMERIC(expression) ABS(number) CEILING(number) FLOOR(number) SQUARE(float_number) SQRT(float_number) RAND([integer]) Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 9 Examples that use the numeric functions Function Result ROUND(12.5,0) ROUND(12.4999,0) ROUND(12.4999,1) ROUND(12.4999,-1) ROUND(12.5,0,1) 13.0 12.0000 12.5000 10.0000 12.0 ISNUMERIC(-1.25) ISNUMERIC('SQL Server') ISNUMERIC('2012-09-30') 1 0 0 Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 10 Examples that use the numeric functions (cont.) Function Result ABS(-1.25) CEILING(-1.25) FLOOR(-1.25) CEILING(1.25) FLOOR(1.25) 1.25 -1 -2 2 1 SQUARE(5.2786) SQRT(125.43) 27.86361796 11.199553562531 RAND() 0.243729 Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 11 The RealSample table How to search for approximate real values A SELECT statement that searches for a range of values SELECT * FROM RealSample WHERE R BETWEEN 0.99 AND 1.01; A SELECT statement that searches for rounded values SELECT * FROM RealSample WHERE ROUND(R,2) = 1; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 12 A SELECT statement that formats real numbers SELECT ID, R, CAST(R AS decimal(9,3)) AS R_decimal, CAST(CAST(R AS decimal(9,3)) AS varchar(9)) AS R_varchar, LEN(CAST(CAST(R AS decimal(9,3)) AS varchar(9))) AS R_LEN, SPACE(9 - LEN(CAST(CAST(R AS decimal(9,3)) AS varchar(9)))) + CAST(CAST(R AS decimal(9,3)) AS varchar(9)) AS R_Formatted FROM RealSample; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 13 Some of the date/time functions GETDATE() GETUTCDATE() SYSDATETIME() SYSUTCDATETIME() SYSDATETIMEOFFSET() DAY(date) MONTH(date) YEAR(date) DATENAME(datepart,date) DATEPART(datepart,date) Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 14 Some of the date/time functions (continued) DATEADD(datepart,number,date) DATEDIFF(datepart,startdate,enddate) TODATETIMEOFFSET(datetime2,tzoffset) SWITCHOFFSET(datetimeoffset,tzoffset) EOMONTH(startdate[,months]) DATEFROMPARTS(year,month,day) ISDATE(expression) Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 15 Date part values and abbreviations Argument year quarter month dayofyear day week weekday Murach's SQL Server 2012, C9 Abbreviations yy, yyyy qq, q mm, m dy, y dd, d wk, ww dw © 2012, Mike Murach & Associates, Inc. Slide 16 Date part values and abbreviations (continued) Argument hour minute second millisecond microsecond nanosecond tzoffset Murach's SQL Server 2012, C9 Abbreviations hh mi, n ss, s ms mcs ns tz © 2012, Mike Murach & Associates, Inc. Slide 17 Examples that use date/time functions Function Result GETDATE() GETUTCDATE() 2012-09-30 14:10:13.813 2012-09-30 21:10:13.813 SYSDATETIME() SYSUTCDATETIME() SYSDATETIMEOFFSET() 2012-09-30 14:10:13.8160822 2012-09-30 21:10:13.8160822 2012-09-30 14:10:13.8160822 -07.00 MONTH('2012-09-30') DATEPART(month,'2012-09-30') DATENAME(month,'2012-09-30') DATENAME(m,'2012-09-30') 9 9 September September Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 18 Examples that use date/time functions (continued) Function Result EOMONTH('2012-02-01') EOMONTH('2012-02-01',2) DATEFROMPARTS(2012,4,3) 2012-02-29 2012-04-30 2012-04-03 ISDATE('2012-09-30') ISDATE('2012-09-31') ISDATE('23:59:59') ISDATE('23:99:99') 1 0 1 0 Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 19 Examples that use the DAY, MONTH, and YEAR functions Function Result DAY('2012-09-30') MONTH('2012-09-30') YEAR('2012-09-30') 30 9 2012 Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 20 Examples that use the DATEPART function Function Result DATEPART(day, '2012-09-30 11:35:00') DATEPART(month, '2012-09-30 11:35:00') DATEPART(year, '2012-09-30 11:35:00') DATEPART(hour, '2012-09-30 11:35:00') DATEPART(minute, '2012-09-30 11:35:00') DATEPART(second, '2012-09-30 11:35:00') DATEPART(quarter, '2012-09-30 11:35:00') DATEPART(dayofyear, '2012-09-30 11:35:00') DATEPART(week, '2012-09-30 11:35:00') DATEPART(weekday, '2012-09-30 11:35:00') DATEPART(millisecond, '11:35:00.1234567') DATEPART(microsecond, '11:35:00.1234567') DATEPART(nanosecond, '11:35:00.1234567') DATEPART(tzoffset, '11:35:00.1234567 -07:00') 30 9 2012 11 35 0 3 273 40 1 123 123456 123456700 -420 Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 21 Examples that use the DATENAME function Function Result DATENAME(day, '2012-09-30 11:35:00') DATENAME(month, '2012-09-30 11:35:00') DATENAME(year, '2012-09-30 11:35:00') DATENAME(hour, '2012-09-30 11:35:00') DATENAME(minute, '2012-09-30 11:35:00') DATENAME(second, '2012-09-30 11:35:00') DATENAME(quarter, '2012-09-30 11:35:00') DATENAME(dayofyear, '2012-09-30 11:35:00') DATENAME(week, '2012-09-30 11:35:00') DATENAME(weekday, '2012-09-30 11:35:00') DATENAME(millisecond, '11:35:00.1234567') DATENAME(microsecond, '11:35:00.1234567') DATENAME(nanosecond, '11:35:00.1234567') DATENAME(tzoffset, '11:35:00.1234567 -07:00') 30 September 2012 11 35 0 3 274 40 Sunday 123 123456 123456700 -07:00 Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 22 Examples that use the DATEADD function Function Result DATEADD(day, 1, '2012-09-30 11:35:00') DATEADD(month, 1, '2012-09-30 11:35:00') DATEADD(year, 1, '2012-09-30 11:35:00') DATEADD(hour, 1, '2012-09-30 11:35:00') DATEADD(minute, 1, '2012-09-30 11:35:00') DATEADD(second, 1, '2012-09-30 11:35:00') DATEADD(quarter, 1, '2012-09-30 11:35:00') DATEADD(week, 1, '2012-09-30 11:35:00') DATEADD(month, -1, '2012-09-30 11:35:00') DATEADD(year, 1.5, '2012-09-30 11:35:00') 2012-10-01 2012-10-30 2013-09-30 2012-09-30 2012-09-30 2012-09-30 2012-12-30 2012-10-07 2012-08-30 2013-09-30 Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. 11:35:00.000 11:35:00.000 11:35:00.000 12:35:00.000 11:36:00.000 11:35:01.000 11:35:00.000 11:35:00.000 11:35:00.000 11:35:00.000 Slide 23 Examples that use the DATEDIFF function Function Result DATEDIFF(day, '2011-12-01', '2012-09-30') DATEDIFF(month, '2011-12-01', '2012-09-30') DATEDIFF(year, '2011-12-01', '2012-09-30') DATEDIFF(hour, '06:46:45', '11:35:00') DATEDIFF(minute, '06:46:45', '11:35:00') DATEDIFF(second, '06:46:45', '11:35:00') DATEDIFF(quarter, '2011-12-01', '2012-09-30') DATEDIFF(week, '2011-12-01', '2012-09-30') DATEDIFF(day, '2012-09-30', '2011-12-01') 304 9 1 5 289 17295 3 44 -304 Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 24 Examples that use the addition and subtraction operators Operation Result CAST('2012-09-30 11:35:00' AS smalldatetime) + 1 2012-10-01 11:35:00 CAST('2012-09-30 11:35:00' AS smalldatetime) – 1 2012-09-29 11:35:00 CAST(CAST('2012-09-30' AS datetime) - CAST('2011-12-01' AS datetime) AS int) 304 Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 25 The contents of the DateSample table A search condition that fails to return a row SELECT * FROM DateSample WHERE StartDate = '2011-10-28'; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 26 SELECT statements that ignore time values Use the date type to remove time values (SQL Server 2008 or later) SELECT * FROM DateSample WHERE CONVERT(date, StartDate) = '2011-10-28'; Search for a range of dates SELECT * FROM DateSample WHERE StartDate >= '2011-10-28' AND StartDate < '2011-10-29'; Search for month, day, and year components SELECT * FROM DateSample WHERE MONTH(StartDate) = 10 AND DAY(StartDate) = 28 AND YEAR(StartDate) = 2011; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 27 SELECT statements that ignore time values (continued) Use the CAST function to remove time values SELECT * FROM DateSample WHERE CAST(CAST(StartDate AS char(11)) AS datetime) = '2011-10-28'; Use the CONVERT function to remove time values SELECT * FROM DateSample WHERE CONVERT(datetime, CONVERT(char(10), StartDate, 110)) = '2011-10-28'; The result set Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 28 The contents of the DateSample table Two search conditions that fail to return a row SELECT * FROM DateSample WHERE StartDate = CAST('10:00:00' AS datetime); SELECT * FROM DateSample WHERE StartDate >= '09:00:00' AND StartDate < '12:59:59:999'; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 29 Two SELECT statements that ignore date values Use the time type to remove date values (SQL Server 2008 or later) SELECT * FROM DateSample WHERE CONVERT(time, StartDate) >= '09:00:00' AND CONVERT(time, StartDate) < '12:59:59:999'; Use the CONVERT function to remove date values (prior to SQL Server 2008) SELECT * FROM DateSample WHERE CONVERT(datetime, CONVERT(char(12), StartDate, 8)) >= '09:00:00' AND CONVERT(datetime, CONVERT(char(12), StartDate, 8)) < '12:59:59:999'; The result set Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 30 The syntax of the simple CASE function CASE input_expression WHEN when_expression_1 THEN result_expression_1 [WHEN when_expression_2 THEN result_expression_2]... [ELSE else_result_expression] END A SELECT statement with a simple CASE function SELECT InvoiceNumber, TermsID, CASE TermsID WHEN 1 THEN 'Net due 10 WHEN 2 THEN 'Net due 20 WHEN 3 THEN 'Net due 30 WHEN 4 THEN 'Net due 60 WHEN 5 THEN 'Net due 90 END AS Terms FROM Invoices; Murach's SQL Server 2012, C9 days' days' days' days' days' © 2012, Mike Murach & Associates, Inc. Slide 31 The syntax of the searched CASE function CASE WHEN conditional_expression_1 THEN result_expression_1 [WHEN conditional_expression_2 THEN result_expression_2]... [ELSE else_result_expression] END A SELECT statement with a searched CASE function SELECT InvoiceNumber, InvoiceTotal, InvoiceDate, InvoiceDueDate, CASE WHEN DATEDIFF(day, InvoiceDueDate, GETDATE()) > 30 THEN 'Over 30 days past due' WHEN DATEDIFF(day, InvoiceDueDate, GETDATE()) > 0 THEN '1 to 30 days past due' ELSE 'Current' END AS Status FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 32 The result set Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 33 The syntax of the IIF function IIF(conditional_expression, true_value, false_value) A SELECT statement with an IIF function SELECT VendorID, SUM(InvoiceTotal) AS SumInvoices, IIF(SUM(InvoiceTotal) < 1000, 'Low', 'High') AS InvoiceRange FROM Invoices GROUP BY VendorID; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 34 The syntax of the CHOOSE function CHOOSE(index, value1, value2 [,value3]...) A SELECT statement with a CHOOSE function SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, CHOOSE(TermsID, '10 days', '20 days', '30 days', '60 days', '90 days') AS NetDue FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 35 The syntax of the COALESCE function COALESCE(expression_1 [, expression_2]...) The syntax of the ISNULL function ISNULL(check_expression, replacement_value) A SELECT statement with a COALESCE function SELECT PaymentDate, COALESCE(PaymentDate, '1900-01-01') AS NewDate FROM Invoices; The same statement with an ISNULL function SELECT PaymentDate, ISNULL(PaymentDate, '1900-01-01') AS NewDate FROM Invoices; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 36 The result set Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 37 A SELECT statement that substitutes a different data type SELECT VendorName, COALESCE(CAST(InvoiceTotal AS varchar), 'No invoices') AS InvoiceTotal FROM Vendors LEFT JOIN Invoices ON Vendors.VendorID = Invoices.VendorID ORDER BY VendorName; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 38 The syntax of the GROUPING function GROUPING(column_name) A summary query with a GROUPING function SELECT CASE WHEN GROUPING(VendorState) = 1 THEN 'All' ELSE VendorState END AS VendorState, CASE WHEN GROUPING(VendorCity) = 1 THEN 'All' ELSE VendorCity END AS VendorCity, COUNT(*) AS QtyVendors FROM Vendors WHERE VendorState IN ('IA', 'NJ') GROUP BY VendorState, VendorCity WITH ROLLUP ORDER BY VendorState DESC, VendorCity DESC; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 39 The result set Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 40 The syntax for the four ranking functions ROW_NUMBER() OVER ([partition_by_clause] order_by_clause) RANK() OVER ([partition_by_clause] order_by_clause) DENSE_RANK() OVER ([partition_by_clause] order_by_clause) NTILE(integer_expression) OVER ([partition_by_clause] order_by_clause) Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 41 A query with a ROW_NUMBER function SELECT ROW_NUMBER() OVER(ORDER BY VendorName) AS RowNumber, VendorName FROM Vendors; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 42 A query that uses the PARTITION BY clause SELECT ROW_NUMBER() OVER(PARTITION BY VendorState ORDER BY VendorName) As RowNumber, VendorName, VendorState FROM Vendors; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 43 A query with RANK and DENSE_RANK functions SELECT RANK() OVER (ORDER BY InvoiceTotal) As Rank, DENSE_RANK() OVER (ORDER BY InvoiceTotal) As DenseRank, InvoiceTotal, InvoiceNumber FROM Invoices; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 44 A query that uses the NTILE function SELECT TermsDescription, NTILE(2) OVER (ORDER BY TermsID) AS Tile2, NTILE(3) OVER (ORDER BY TermsID) AS Tile3, NTILE(4) OVER (ORDER BY TermsID) AS Tile4 FROM Terms; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 45 The syntax of the analytic functions {FIRST_VALUE|LAST_VALUE}(scalar_expression) OVER ([partition_by_clause] order_by_clause [rows_range_clause]) {LEAD|LAG}(scalar_expression [, offset [, default]]) OVER ([partition_by_clause] order_by_clause) {PERCENT_RANK()|CUME_DIST} OVER ([partition_by_clause] order_by_clause) {PERCENTILE_CONT|PERCENTILE_DISC}(numeric_literal) WITHIN GROUP (ORDER BY expression [ASC|DESC]) OVER (partition_by_clause) Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 46 The columns in the SalesReps table Column name RepID RepFirstName RepLastName Data type int varchar(50) varchar(50) The columns in the SalesTotals table Column name RepID SalesYear SalesTotal Murach's SQL Server 2012, C9 Data type int char(4) money © 2012, Mike Murach & Associates, Inc. Slide 47 A query that uses the FIRST_VALUE and LAST_VALUE functions SELECT SalesYear, RepFirstName + ' ' + RepLastName AS RepName, SalesTotal, FIRST_VALUE(RepFirstName + ' ' + RepLastName) OVER (PARTITION BY SalesYear ORDER BY SalesTotal DESC) AS HighestSales, LAST_VALUE(RepFirstName + ' ' + RepLastName) OVER (PARTITION BY SalesYear ORDER BY SalesTotal DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LowestSales FROM SalesTotals JOIN SalesReps ON SalesTotals.RepID = SalesReps.RepID; Murach's SQL Server 2012, C9 © 2012, Mike Murach & Associates, Inc. Slide 48