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.



-
Published Tuesday, March 4, 2008 2:25 PM by simonsabin
Filed under: ,

Comments

Sunday, April 20, 2008 8:50 PM by sql replace substring

# sql replace substring

Pingback from  sql replace substring

Tuesday, May 8, 2012 1:32 PM by Convert Text String to Numbers (Int) | Monir's Blog

# Convert Text String to Numbers (Int) | Monir&#039;s Blog

Pingback from  Convert Text String to Numbers (Int) | Monir&#039;s Blog

# replace and convert | Dummies Guide to SQL for THIS dummy

Pingback from  replace and convert | Dummies Guide to SQL for THIS dummy