Function for remove accent marks in SQL Server

Photo by Courtney Corlew

A common issue when we are creating a search functionality is to exclude results due to accent marks. For example, if we search by name or surname of an employee, we could exclude rows due to accents marks. One scenario could be the following, if we are looking for all the matches for the name Paéz, the SQL statement would omit users with the name Paez from the query, to avoid this we will create a function to remove accents in SQL Server.

CREATE FUNCTION RemoveAccentMarks ( @Cadena VARCHAR(100) )
    RETURNS VARCHAR(100)
AS 
BEGIN
 
    --Replace accent marks
    RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Cadena, 'á', 'a'), 'é','e'), 'í', 'i'), 'ó', 'o'), 'ú','u') 

END

Now you call the function where you need it.

SELECT  dbo.RemoveAccentMarks ( 'Paéz' )

Do you have other style for remove accent marks? Please let me know.


Leave a Reply

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