The big use-case is around addresses. I often see people cleansing their data around addresses, looking to match addresses which are the same, but spelled slightly wrong or using abbreviations in a slightly different way. Currently, SSIS is the only way of doing this. It's seen as the cleansing tool.
SSIS is great for cleaning the data and reloading it in a cleaner format. But often you don't want to do that - you just want a query which can do fuzzy grouping or fuzzy joining.
Of course in the SSIS fuzzy transforms there are a lot of options that can be configured. Support in the engine would need to be able to have database-level settings to provide defaults, and any functions would need to be able to alter them.
I'm thinking something like:
GROUP BY City, FUZZY(Address1 + ' ' + Address2, <options>)
And predicates something like:
WHERE a.Address1 FUZZYMATCH(<options>) b.Address1
or... if you have boolean types:
WHERE FUZZYMATCH(a.Address1, b.Address1, <options>)
With this, cleansing could be done using a T-SQL statement. INSERT statements could become MERGE statements which look to find existing _similar_ records before inserting a new one.