Over at Red Gate’s Simple-Talk blog, Robyn Page and ‘Phil Factor’ have a nice article on using a helper table of numbers to convert some common T-SQL iterative tasks to set-based operations:
- Splitting Strings into table-rows, based on a specified delimiter
- Encoding and decoding a string
- Substituting values into a string
- Extracting individual words from a string into a table
- Extracting all the numbers in a string into a table
- Removing all text between delimiters
- Scrabble score
- Moving averages
- Getting the ‘Week beginning’ date in a table
- Calculating the number of working days between dates
Rob Farley is a bit of a demon with a table of numbers; I wonder if knows any more of these? Rob?