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:

CREATE FUNCTION [dbo].[ufnUnixFriendly]
(
@myString VARCHAR(255)
)
RETURNS VARCHAR(255)
WITH SCHEMABINDING
AS
BEGIN
-- Declare the return variable here
DECLARE @newString VARCHAR(255);

-- Make the string case and accent insesitive
SET @newString = @myString COLLATE SQL_Latin1_General_CP1_CI_AI;

-- Remove leading and trailing spaces
SET @newString = LTRIM(RTRIM(@newString))

-- Replace spaces by underscores
SET @newString = REPLACE(@newString, ' ', '_')

-- Replace hyphens by underscores
SET @newString = REPLACE(@newString, '-', '_')

-- Remove special characters
DECLARE @pos INT
SET @pos = 1
WHILE @pos <= LEN(@newString)
BEGIN
IF SUBSTRING(@newString, @pos, 1) NOT LIKE '[a-z_0123456789]'
SET @newString = STUFF(@newString, @pos, 1, '')
ELSE
SET @pos = @pos + 1
END

-- Replace multiple underscores by single underscores
DECLARE @len INT
SET @len = 999999
WHILE @len > LEN(@newString)
BEGIN
SET @len = LEN(@newString)
SET @newString = REPLACE(@newString, '__', '_')
END

-- Replace letters by equivalent lower case letters without accents
-- Because @newString is using an accent and case insensitive collation,
-- doing a REPLACE(@newString, 'a', 'a') will also replace 'à' with 'a'
-- and 'A' with 'a'.
DECLARE @char CHAR(1)
DECLARE @code INT
SET @code = ASCII('a')
WHILE @code <= ASCII('z')
BEGIN
SET @char = CHAR(@code)
SET @newString = REPLACE(@newString, @char, @char)
SET @code = @code + 1
END

-- Return the result of the function
RETURN @newString

END

Leave a Reply

Your email address will not be published. Required fields are marked *