A SQL Server Database project with some useful SQL CLR functions. Requires Visual Studio with SQL Server Data Tools (SSDT) installed.

AdHoc/SqlClr.sql
Run on SQL Server instance to enable SQL CLR

UsefulSql.publish.xml
Publishes a database called UsefulSql to SQL Instance localhost

dbo.GetFiles
Returns a list of all files in a directory (including all subfolders)

SELECT * FROM UsefulSql.dbo.GetFiles('c:\myfolder')

dbo.GetFileContents
Returns the contents of a file

SELECT UsefulSql.dbo.GetFileContents('c:\myfolder\myfile.txt')

dbo.GetUnicodeValues
Returns all characers from a string with their unicode integer values.

SELECT *
FROM 
	UsefulSql.dbo.GetUnicodeValues('some text')

dbo.ReplaceUnicodeValues
Returns a string with one character code replaced with another.

-- space = 32
-- underscore = 95
-- returns replace_spaces_with_underscores
SELECT UsefulSql.dbo.ReplaceUnicodeValues('replace spaces with underscores', 32, 95)	

The following regex related functions all have the following parameters

dbo.RegexGetMatches
Wrapper around Regex.Matches. Returns a row for each match.

SELECT * 
FROM 
	UsefulSql.dbo.RegexGetMatches('Extract individual words', '\b(\w+)\b', NULL)

dbo.RegexGetMatch
Wrapper around Regex.Match. Always returns a single row with the match result.

SELECT * 
FROM 
	UsefulSql.dbo.RegexGetMatch('Gets first match only', '\b(\w+)\b', NULL)

dbo.RegexIsMatch
Wrapper around Regex.IsMatch. Scalar function, returning a BIT indicating if the expression matches.

SELECT UsefulSql.dbo.RegexIsMatch('Returns 1 or 0', '\b(\w+)\b', NULL)

dbo.RegexGetGroups
Returns the values of groups defined in the regular expression.

SELECT * 
FROM 
	UsefulSql.dbo.RegexGetGroups('Dogs:20;Cats:10', '^Dogs\:?(?<Dogs>\d+);Cats\:?(?<Cats>\d+)$', NULL)

SELECT * 
FROM 
	UsefulSql.dbo.RegexGetMatch('Gets first match only', '\b(\w+)\b', NULL)

dbo.GetXPathValue
Returns the first matching result of an XPath expression for the given XML.

SELECT UsefulSql.dbo.GetXPathValue('<root><item key="1" value="AAA" /></root>', '//root/item[@key="1"]/@value')

dbo.GetXPathValues
Returns all matching results of an XPath expression for the given XML.

SELECT *
FROM
	UsefulSql.dbo.GetXPathValues('<root><item key="1" value="AAA" /><item key="2" value="BBB" /></root>', '//root/item/@value')