SQLUtilities

SQLUtilities includes:

• 21 date and time related functions, including a table-valued date and time sequence generator (allowing you to generate any required set of incremental dates or times of any size on the fly without having to create and maintain lookup tables); random date and time generators; working days calculations; date and time arithmetic; formatting, parsing and type conversion.
• 37 string and text processing functions including string concatenation functions and aggregates; scalar and table-valued tokenized string parsing; advanced formatting, searching and substitution; regular expressions processing; collation-free comparisons; type conversions.
• 3 very useful numeric functions - including a table-valued numberline sequence generator - allowing you to generate any required set of incremental numeric values of any size on the fly without having to create and maintain lookup tables, and two excel-like random number generators that are easier to use than the built-in T-SQL RAND() function.
• 3 relative column reference aggregate functions, allowing you to select values from multiple columns in an aggregation from the row that contains the maximum, minimum or a matching value of one column.

Beyond Excel
Some of the functions in SQLUtilities are similar in name and function to the date and string processing functions found in Microsoft Excel and similar packages - although generally with significantly expanded functionality and range. There are also many others that are over and above what can be found in Excel. Combined with the innate power and ability of SQL Server to rapidly crunch through huge volumes of data - this gives you a data processing capability that completely blows away anything you could do with spreadsheets.

Easy to Use
All TotallySQL Transact-SQL extensions are implemented using familiar programming objects within SQL (scalar and table-valued functions, aggregate functions, user-defined types, stored procedures) , keeping the syntax clean and making them as fast and easy to use as the built-in native functions such as LTRIM and DATEADD.

SQLUtilities Features

• Return a table of numbers increasing / decreasing at set intervals between two bounds.
• Return a random float between 0 & 1 or a random integer between 2 bounds. No seeding.
• Return a table of dates, times or datetimes at any fixed interval between two bounds.
• Generate a random date, a random time or a random datetime between two bounds.
• Strip out the time or the date component of a datetime, leaving the date only or time only.
• Constructors for date, time and datetimes based on components (year, month, day etc).     
• Return start date or end date of the month that is n months before or after the starting date.  
• Count of working days between two dates, or in a given month, allowing for weekends & holidays. 
• Detect whether a given day is a working day, allowing for weekends & holidays.                    
• Find date of first working day that is n working days before or after given date.     
• Add fractional number of days to a datetime (e.g. 1.5 days = 18 hours).                             
• Detect whether given date is the last day of month, or whether given year is a leap year.     
• Return date of nth occurence  of mth weekday in a given month - e.g. 3rd Tuesday.            
• Accurate conversion between SQL datetime values and Excel datetime serial numbers
• Count of total days in given month
• Format any SQL type as a string using .NET format specifier strings (e.g. "dd-mm-yyyy").      
• Concatenate 2 strings or aggregate strings from multiple rows into a single, delimited string.        
• Break delimited strings into tokens & return a table of them all, or a count, or a given token.
• Remove non-printing, punctuation, whitespace or listed characters from part or all of a string.      
• Compare two strings for order or equality, ignoring collation, & case if specified.           
• Flag if one string starts or ends with or contains another, ignoring collation & case if specified.        
• Find first or last occurrence or count occurrences of one string in another, ignoring collation etc.     
• Find first or last occurrence of any listed character in string.                                            
• Remove n characters from string at position m, or insert substring at position m.
• Add spaces or other characters to start or end, or evenly, to pad string out to desired width.           
• Parse a string as the specified SQL type & return as an object of that type (in a sql_variant)        
• Find the first, nth or last match, flag if any match, or count matches, of regular expression in string.    
• Return table of all matches to given regular expression in string, giving start, length & matched text. .
• Replace all matches to given regular expression with supplied replacement pattern.        
• Return carriage return / line feed combination.
• Convert string to proper case (first letter of each word in upper case, rest in lower case).
• Return the value of one column at the maximum, minimum or specified value of another.