These are simple functions I've written in order to avoid spending $500 for a two year license (https://sqlsharp.com/full/) of SQL#. For one, the product hasn't been updated in 2 years, and that's not very cool for a pay product.
FYI, warning, buyer beware on my stuff: I use UNSAFE assemblies, because half the reason to use SQLCLR is to get to functions that are not in SQL Server already, which is where the safe stuff is.
Another bug I have with SQL# is that their names are overly precious. They will name a function SQL#.string_Is_BEGINNING_WITH whereas I call it StartsWith, which is not coincidentally the same as the C# method. I try to align my names with C# when the functionality aligns. Less to remember.
I also have less arguments on functions than SQL#. SQL Server does not support optional arguments on functions, and so these are annoying to have to fill out. Also, I forget what they are supposed to be, and I don't recall their documentation telling me if using DEFAULT will work. Rather than every possible argument under the sun, I do it the old fashion way: I make a new function. For example, hypothetically, I would do this:
For reduced maintenance, I pass everything as NVARCHAR(MAX) as well as return NVARCHAR(MAX). Speed is not my main problem. The main problem is that SQL Server functions are severely lacking and new functions are added every third decade. STRING_AGG is great, but a SQLCLR function can go back to at least 2012.
I do suspect that there may be memory allocation issues with this design, and so I may come up with a generative way to make VARCHAR(8000) or NVARCHAR(4000) clones. Due to the way SQL Server pre-allocates memory, it may be even better to support smaller sizes. Not really sure.
If it's possible to think of a logical algorithm that can easily be described and understood, and there's no confusion about what to expect in the output, then it's worth being a function.
For instance, I have a function called AnyOneOfTheseIsInThose, or something like that. It is for when I to know if there is a non-empty intersection of a list of values that I don't want to stuff into tables. It answers the simple question: Are any of these items in this other list?
To pass in lists, I use delimited strings, but I usually have an argument for the separating string. SPLIT_STRING from Microsoft, and most home-grown SQLCLR splitters seem to think all splitting is based on single characters. I support the string for the cases that come up.
HowMany. Simple enough! How many commas? We are pulling in a CSV, and missing commas or extras will break it.
My favorites have to be Matches, Ping, StartsWith, EndsWith, AnyOfTheseeAreAnyOfThose, HowMany, LeftOf, LeftOfNth, LeftOfNth, IndexOfLast Also GetFirstName, Pieces, RTrimChar.
I have a bunch somewhere, and I need to add more.
- Better captures of stored procedure output or xp_cmdshell return strings. - Escape function for multi-level dynamic SQL generation. - A UNIX cut command for fixed-width. - CSV, XML, Excel in/out. - HTML table generation for emails. - Intelligent Substring Title casing, like "ROTC" is not "Rotc". - Smarter name normalization, accent removals for us Americanos. - SQL formating more to my tastes. - Some default formatters, like LogFileTimeStamp = "YYYYmmDDhh24missffffff" or some such. So I don't have to remember. - Pivot code I found for true column-to-row pivot. - Possibly push SQL work down? Like a TRUNCATE TABLE that pre-strips FKs, truncates, reloads, and tries to add the FKs back. - Unix to/from for Active Directory columns. - Natural language extraction of patterned speech.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent