SQL Sunday Fun – Word Search with New T-SQL 2012 Functionality

 

 

new_sql2012_word_jumbleClick on the grid to enlarge, then print it.

Find the following words in the grid up, down, left, right, horizontal, vertical, and diagonal.

ROWS  RANGE  PRECEDING 
FOLLOWING  UNBOUNDED  IIF 
CHOOSE  OFFSET  FETCH 
FORMAT  CONCAT  SEQUENCE 
PARSE  TRYPARSE  TRYCONVERT 
THROW  LEAD  LAG 
FIRSTVALUE  LASTVALUE  PERCENTRANK 
CUMEDIST  PERCENTILEDISC  PERCENTILECONT 

NOTE:  some of these terms normally have an underscore in them, but for the purpose of the word scramble the underscores have been omitted.

 

Related Links:

SEQUENCE object

CHOOSE function

Analytics - Analytic Functions – PERCENT_RANK

Analytics PERCENTILE_DISC and PERCENTILE_CONT

IIF function in TSQL.

TSQL Analytic Functions LEAD and LAG

OVER clause with ROWS PRECEDING and FOLLOWING in TSQL 2012

OFFSET / FETCH in a SELECT statement

Cumulative Distribution Function (CDF) or as it is known in TSQL CUME_DIST()

 

 

SQL Saturday Presentation

I am at SQL Saturday in Vancouver BC today.

Here is the download of my presentation.

Download zip file.

Here is the presentation outline.

  • OVER Clause Enhancements
  • ROWS PRECEDING, FOLLOWING, UNBOUNDED
  • RANGE PRECEDING, FOLLOWING, UNBOUNDED
  • IIF – Immediate IF or Inline IF (from Access)
  • CHOOSE (from Access)
  • OFFSET / FETCH
  • FORMAT
  • CONCAT
  • SEQUENCE (from Oracle)
  • sp_describe_first_result_set
  • New Date and Time Functions
  • Conversion Functions
  • PARSE, TRY_PARSE, TRY_CONVERT
  • THROW exception

TSQL 2012 CHOOSE Function

In SQL Server 2012 there is a new function called CHOOSE that takes in an offset, and a list of options.  Choose is a function that returns the item at a specific index.

Syntax:
—CHOOSE(index, val_1, val_2, val_3, …)
—If the index is greater than the number of values or less than 1 it returns NULL
—Easier than using a CASE statement in some examples.
Example:
</div>
<div>
-- CHOOSE
-- returns the item at a specific index
declare @corners as int = 6
SELECT choose(@corners, 'point', 'line', 'triangle', 'square',
 'pentagon', 'hexagon', 'heptagon', 'octagon')

-- the old way using case.
SELECT CASE @corners
 WHEN 1 THEN 'point'
 WHEN 2 THEN 'line'
 WHEN 3 THEN 'triangle'
 WHEN 4 THEN 'square'
 WHEN 5 THEN 'pentagon'
 WHEN 6 THEN 'hexagon'
 WHEN 7 THEN 'heptagon'
 WHEN 8 THEN 'octagon'
 else NULL
 END;

In the above example, the CASE statement and CHOOSE function do the same thing, but the CHOOSE function is a bit easier to write.Here is another example

-- CHOOSE day of week example
DECLARE @day as int=4
SELECT CHOOSE(@day,'Sunday','Monday', 'Tuesday',
              'Wednesday','Thursday','Friday','Saturday')
For choose and other new TSQL functionality in Server 2012 take a look at my What’s New in TSQL 2012 presentation, which will be presented at Seattle Code Camp on June 16th.