Replace invalid chracters with valid character
I am updating the SQLBits website to have the content
from the weekend and I wanted to have the session titles in the URL. However
many of the titles contain invalid characters (well maybe not invalid ones but
ones that are escaped).
So to avoid URLS like /Agenda/I/O!%20I/O!%20It%e2%80%99s%20off%20to%20disk%20we%20go I
wanted something a bit nicer on the eye.
This is easy to do with a slight modification to the code in String
cleaning with TSQL.
select cast(cast((select case when substring(title,n,1) like '[a-z0-9/-]'
then substring(title,n,1) else '_' end
from num
where n <= len(title)
for xml
path('')) as xml)as varchar(max))
Rather than ignoring the character I check its value and substitue it with an
underscore "_".
This would be even easier with CLR code however the hosting providers of SQL
Bits don't allow CLR code in the database.
-