How to replace accented characters with non-accented ones

Question

Post reply

How to replace accented characters with non-accented ones

xavier.villafuerte

SSC-Addicted

Points: 449
More actions
September 18, 2008 at 1:28 pm

#192928

Hi, I am looking for a SQL function which converts (not remove) a string containing accented characters into the same string without the accented characters.
For example: select myfunction('hóla') should return hola
Any help would be much appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Quatrei.X SSCrazy Points: 2697 More actions · Answer 1

Hi there,

Try this one

SELECT REPLACE('hóla','ó','o')

Tell me if this was helpful or if it needs some modifications.. Thank you ^__^

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

xavier.villafuerte SSC-Addicted Points: 449 More actions · Answer 2

Thanks. I used your function and added a few more concatenated replace sentences to deal with the possible accented characters in any string.

Thanks again

Quatrei.X SSCrazy Points: 2697 More actions · Answer 3

You welcome ^__^

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

dmabo SSC Enthusiast Points: 102 More actions · Answer 4

I have written my own fucntion.

You can test it with:

SELECT dbo.Format_RemoveAccents( 'ñaàeéêèioô; Œuf un œuf' )

You will optain ==> naaeeeeioo; OEuf un oeuf

The function:

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

sacha.bessard Old Hand Points: 370 More actions · Answer 5

http://www.xoowiki.com/Article/Csharp/remplacer-les-accents-dans-une-chaine-373.aspx

Paul White SSC Guru Points: 150467 More actions · Answer 6

xavier.villafuerte (9/18/2008)
Hi, I am looking for a SQL function which converts (not remove) a string containing accented characters into the same string without the accented characters.

I just want to check that you want to remove accents permanently? If you are just doing it to make string comparisons work as you would like, you could just use an explicitly accent-insensitive collation:

DECLARE @String CHAR(22) = N'ñaàeéêèioô; Œuf un œuf'

;

IF @String COLLATE LATIN1_GENERAL_CS_AS = N'naaeeeeioo; Œuf un œuf'

PRINT 'Match 1'

;

IF @String COLLATE LATIN1_GENERAL_CS_AI = N'naaeeeeioo; Œuf un œuf'

PRINT 'Match 2'

;

^^^ Prints 'Match 2' only ^^^

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi