Solution - Convert your punctuated chars - SQL Script

[xtech]

Hi guys,

in the process of converting to 4.0.x, i had to manually migrate some apps (ex: classifieds, company directory, etc) to my own IP. Pages databases.
In the process i had some FURLS that had punctuated characters (including the € symbol) and the records were not opening. So i had to convert them to non punctuated characters.

If you find the problems, i have here a small script to share:

#Convert accent characters

#a
update ibf_cms_custom_database_7
set record_dynamic_furl = replace(record_dynamic_furl, '%c3%a1', 'a')
where record_dynamic_furl like '%\%%'

update ibf_cms_custom_database_7
set record_dynamic_furl = replace(record_dynamic_furl, '%c3%a3', 'a')
where record_dynamic_furl like '%\%%'

update ibf_cms_custom_database_7
set record_dynamic_furl = replace(record_dynamic_furl, '%c3%a0', 'a')
where record_dynamic_furl like '%\%%'

update ibf_cms_custom_database_7
set record_dynamic_furl = replace(record_dynamic_furl, '%c3%a2', 'a')
where record_dynamic_furl like '%\%%'

update ibf_cms_custom_database_7
set record_dynamic_furl = replace(record_dynamic_furl, '%c2%a', 'a')
where record_dynamic_furl like '%\%%'


#e
update ibf_cms_custom_database_7
set record_dynamic_furl = replace(record_dynamic_furl, '%c3%a9', 'e')
where record_dynamic_furl like '%\%%'

update ibf_cms_custom_database_7
set record_dynamic_furl = replace(record_dynamic_furl, '%c3%aa', 'e')
where record_dynamic_furl like '%\%%'

#i
update ibf_cms_custom_database_7
set record_dynamic_furl = replace(record_dynamic_furl, 'c3%ad', 'i')
where record_dynamic_furl like '%\%%'

#o
update ibf_cms_custom_database_7
set record_dynamic_furl = replace(record_dynamic_furl, '%c3%b3', 'o')
where record_dynamic_furl like '%\%%'

update ibf_cms_custom_database_7
set record_dynamic_furl = replace(record_dynamic_furl, '%c3%b5', 'o')
where record_dynamic_furl like '%\%%'

#u
update ibf_cms_custom_database_7
set record_dynamic_furl = replace(record_dynamic_furl, '%c3%ba', 'u')
where record_dynamic_furl like '%\%%'

#ç
update ibf_cms_custom_database_7
set record_dynamic_furl = replace(record_dynamic_furl, '%c3%a7', 'c')
where record_dynamic_furl like '%\%%'

#€
update ibf_cms_custom_database_7
set record_dynamic_furl = replace(record_dynamic_furl, '%e2%82%ac', 'euros')
where record_dynamic_furl like '%\%%'

Replace the ibf_cms_custom_database_7 to the table you need to update, the same with record_dynamic_furl.
Feel free to update this script here with more punctuated characters you may find.
This works with:

%c3%a1 - á
%c3%a3 - ã
%c3%a0 - à
%c3%a2 - â
%c2%a - ª
%c3%a9 - é
%c3%aa - ê
%c3%ad - í
%c3%b3 - ó
%c3%b5 - õ
%c3%ba - u
%c3%a7 - ç
%e2%82%ac - €