I am working on a database containing city names in French, and I needed to use these names to create URLs. However, the city names contain accents and special characters like apostrophe that I did not want in my URLs. So I had to write a User Defined Function to make the city names Unix firendly:<\/p>\n
CREATE FUNCTION [dbo].[ufnUnixFriendly]\r\n(\r\n@myString VARCHAR(255)\r\n)\r\nRETURNS VARCHAR(255)\r\nWITH SCHEMABINDING\r\nAS\r\nBEGIN\r\n-- Declare the return variable here\r\nDECLARE @newString VARCHAR(255);\r\n\r\n-- Make the string case and accent insesitive\r\nSET @newString = @myString COLLATE SQL_Latin1_General_CP1_CI_AI;\r\n\r\n-- Remove leading and trailing spaces\r\nSET @newString = LTRIM(RTRIM(@newString))\r\n\r\n-- Replace spaces by underscores\r\nSET @newString = REPLACE(@newString, ' ', '_')\r\n\r\n-- Replace hyphens by underscores\r\nSET @newString = REPLACE(@newString, '-', '_')\r\n\r\n-- Remove special characters\r\nDECLARE @pos INT\r\nSET @pos = 1\r\nWHILE @pos <= LEN(@newString)\r\nBEGIN\r\nIF SUBSTRING(@newString, @pos, 1) NOT LIKE '[a-z_0123456789]'\r\nSET @newString = STUFF(@newString, @pos, 1, '')\r\nELSE\r\nSET @pos = @pos + 1\r\nEND\r\n\r\n-- Replace multiple underscores by single underscores\r\nDECLARE @len INT\r\nSET @len = 999999\r\nWHILE @len > LEN(@newString)\r\nBEGIN\r\nSET @len = LEN(@newString)\r\nSET @newString = REPLACE(@newString, '__', '_')\r\nEND\r\n\r\n-- Replace letters by equivalent lower case letters without accents\r\n-- Because @newString is using an accent and case insensitive collation,\r\n-- doing a REPLACE(@newString, 'a', 'a') will also replace '\u00e0' with 'a'\r\n-- and 'A' with 'a'.\r\nDECLARE @char CHAR(1)\r\nDECLARE @code INT\r\nSET @code = ASCII('a')\r\nWHILE @code <= ASCII('z')\r\nBEGIN\r\nSET @char = CHAR(@code)\r\nSET @newString = REPLACE(@newString, @char, @char)\r\nSET @code = @code + 1\r\nEND\r\n\r\n-- Return the result of the function\r\nRETURN @newString\r\n\r\nEND<\/pre>\n","protected":false},"excerpt":{"rendered":"I am working on a database containing city names in French, and I needed to use these names to create URLs. However, the city names contain accents and special characters like apostrophe that I did not want in my URLs. So I had to write a User Defined Function to make the city names Unix […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[3],"tags":[],"class_list":["post-109","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005"],"_links":{"self":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/109","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/comments?post=109"}],"version-history":[{"count":5,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/109\/revisions"}],"predecessor-version":[{"id":187,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/109\/revisions\/187"}],"wp:attachment":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/media?parent=109"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/categories?post=109"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/tags?post=109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}