I have had numerous occassions where I needed to find the most recently modified or created file from a specific path and return this value along with the full path.  The below clr function accepts three parameters:

1. File path,
2. Criteria of "created" or "modified", specifies the criteria of the most recent date to search for
3. File extension, a value of  "*" will search all file extensions.

This code uses the System.IO namespace and requires access the the file system so it is necessary to deploy the assembly with external access permission level. 

It should be noted that the return value is set to SqlString.  If a file not matching the criteria is not located or the path is invalid then the string value of"NULL' is returned.  This is a string value and not a sql NULL value.  

This example provides a framework for the SqlBulkCopy class and its implementation in SQL CLR.

This example covers creating a generic user defined function to allow the use of regular expressions in SQL Server for pattern matching.  This can then replace the pattern matching in tsql.