I think that the CLR is great for doing things not natively available in T-SQL, such as RegEx, File System, and Internet related functions (just to name a few). Also, sometimes it nice just to be able to encapsulate some algorithms that maybe can be done in straight T-SQL but T-SQL is more for set-based operations than doing some computations or string manipulation.
I have been working on a CLR project for 3 years now and it has gotten a much better response than one would expect given some of the negativity towards using CLR at all. The project -- SQL# (SQLsharp) -- is just a library of some common and some less common functions. Some were just interesting to produce even if there is little practical application and some have certainly made my life easier. Unfortunately with over 150 functions I am not sure which ones are used more than others by those who download it, but personally I have used the File System functions to export data, GZip files, copy files between servers, etc. There are plenty of back-end operations that are not set-based and are called by SQL Agent where being able to accomplish more within T-SQL makes us DB folks less reliant upon application developers who might not have time to help us with certain projects. Some will argue that some of these operations should still be done by business logic outside of the DB, but we all have jobs to do and we have to use what means are available to us. For those who are most comfortable with T-SQL, doing many of these functions simply allows people to get their job done in a language that is familiar, can be easily maintained, and doesn't require bringing on additional people (which can mean getting the job done now instead taking an additional 2 months).
Also, creating a User-Defined Aggregate to get a Median has helped.
Lastly, the most unexpected positive response has been the desire to access Twitter via SQL Server. Originally I incorporated that functionality because it seemed interesting (even if I was unsure as to how useful it was) but the number of downloads I am getting has increased dramatically since adding the Twitter functions.
So, I think that CLR functionality has been very helpful for many people, it is just that they are not being very vocal about it.
SQL# - http://www.SQLsharp.com/