Skip to content

Instantly share code, notes, and snippets.

@javierguerrero
Created June 12, 2012 21:00
Show Gist options
  • Save javierguerrero/2920107 to your computer and use it in GitHub Desktop.
Save javierguerrero/2920107 to your computer and use it in GitHub Desktop.
How to replace accented characters with non-accented ones
CREATE FUNCTION dbo.Format_RemoveAccents( @Str varchar(8000) )
RETURNS varchar(8000)
AS
BEGIN
/*
EXEMPLE :
SELECT dbo.Format_RemoveAccents( 'ñaàeéêèioô; Œuf un œuf' )
==> naaeeeeioo; OEuf un oeuf
By Domilo
*/
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'a', 'a' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'e', 'e' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'i', 'i' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'o', 'o' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'u', 'u' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'y', 'y' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'n', 'n' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'œ', 'oe' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'æ', 'ae' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'ß', 'ss' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 's', 's' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'A', 'A' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'E', 'E' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'I', 'I' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'O', 'O' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'U', 'U' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'Y', 'Y' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'N', 'N' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'Œ', 'OE' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'Æ', 'AE' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'ß', 'SS' )
SET @Str = Replace( @Str COLLATE Latin1_General_CS_AI, 'S', 'S' )
RETURN @Str
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment