locked
Accented Words Mapping to English Alphabet RRS feed

  • Question

  • How to translate accented words to plain English alphabet?

    Example: el camaleón  --> el camaleon

    Thanks.

    -- no conversion
    DECLARE @Foreign nvarchar(20) = N'el camaleón';
    SELECT @Foreign as [Foreign], CONVERT(varchar(20), @Foreign) as English
    /*
    Foreign	English
    el camaleón	el camaleón
    */
    
    
    -- some conversion
    DECLARE @Foreign nvarchar(20) = N'erősítése';
    SELECT @Foreign as [Foreign], CONVERT(varchar(20), @Foreign) as English
    /*
    erősítése	erosítése
    */


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012






    Tuesday, June 10, 2014 10:05 AM

Answers

  • DECLARE @Foreign nvarchar(20) = N'el camaleón';
    SELECT @Foreign as [Foreign], 
           convert(varchar(20), @Foreign) COLLATE Cyrillic_General_CI_AS as English
    /*
    Foreign	English
    el camaleón	el camaleón
    */
    go
    
    -- some conversion
    DECLARE @Foreign nvarchar(20) = N'erősítése';
    SELECT @Foreign as [Foreign], 
           convert(varchar(20), @Foreign) COLLATE Cyrillic_General_CI_AS as English

    Obviously it's not going to work out, if there are Cyrillic letters in the text. But for these there is no quick fix any way, I believe.

    And of course, it will also wreck good English works like naïve, rôle and résumé.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Naomi N Tuesday, June 10, 2014 10:08 PM
    • Marked as answer by Kalman Toth Wednesday, June 11, 2014 7:22 AM
    Tuesday, June 10, 2014 10:01 PM

All replies

  • DECLARE @Foreign nvarchar(20) = N'el camaleón';
    SELECT @Foreign as [Foreign], 
           convert(varchar(20), @Foreign) COLLATE Cyrillic_General_CI_AS as English
    /*
    Foreign	English
    el camaleón	el camaleón
    */
    go
    
    -- some conversion
    DECLARE @Foreign nvarchar(20) = N'erősítése';
    SELECT @Foreign as [Foreign], 
           convert(varchar(20), @Foreign) COLLATE Cyrillic_General_CI_AS as English

    Obviously it's not going to work out, if there are Cyrillic letters in the text. But for these there is no quick fix any way, I believe.

    And of course, it will also wreck good English works like naïve, rôle and résumé.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Naomi N Tuesday, June 10, 2014 10:08 PM
    • Marked as answer by Kalman Toth Wednesday, June 11, 2014 7:22 AM
    Tuesday, June 10, 2014 10:01 PM
  • Thanks Erland. You are simply amazing!

    DECLARE @Foreign nvarchar(20) = N'el camaleón';
    SELECT @Foreign as [Foreign], 
           convert(varchar(20), @Foreign) COLLATE Cyrillic_General_CI_AS as English
    /*
    Foreign	English
    el camaleón	el camaleon
    */
    go
    
    -- some conversion
    DECLARE @Foreign nvarchar(20) = N'erősítése';
    SELECT @Foreign as [Foreign], 
           convert(varchar(20), @Foreign) COLLATE Cyrillic_General_CI_AS as English
    
    -- erősítése	erositese



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012







    • Edited by Kalman Toth Wednesday, June 11, 2014 12:48 AM
    Wednesday, June 11, 2014 12:46 AM