0

I would like to use a function convert none Latin characters to Latin characters when producing a report from the data in the DB, which is Azure SQL Server. i.e. input the original name and output the name with the latin characters

For example if a name is written with one of the following:

Ä ä À à Á á Â â Ã ã Å å Ǎ ǎ Ą ą Ă ă Æ æ

I would like to replace the non-latin characters with A or a.

If a name is written with one of the following:

Ç ç Ć ć Ĉ ĉ Č č

I would like to replace the non-latin characters with C or c.

This is the list of non-latin characters to replace. It is quite long so I am asking if anyone knows a smart way to do this rather than brute force using so many replace operations:

enter image description here

Thank you for your help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1372603
  • 47
  • 1
  • 6

1 Answers1

1

Use COLLATE SQL_Latin1_General_CP1253_CI_AI in your SELECT statements.

For example:

SELECT 'Ä ä À à Á á Â â Ã ã Å å Ǎ ǎ Ą ą Ă ă Æ æ' COLLATE SQL_Latin1_General_CP1253_CI_AI as [result]

It will work for almost all characters specified in your image except few like Æ and æ. You can use TRANSLATE for remaining characters if it is really required.

You can read more from here.

Ravi Desai
  • 140
  • 1
  • 10
  • Thanks. Not sure though how I would use Translate for those given the replacing characters are actually two characters. Wouldnt I need to translates? Translate (@first_name,'Ææ','Aeae') – user1372603 Apr 08 '20 at 14:10
  • 1
    checkout this [fiddle](http://sqlfiddle.com/#!18/9eecb/82360). Make a note that TRANSLATE is available in SQL Server 2017 and later. Use REPLACE if you are using earlier SQL Server 2017 – Ravi Desai Apr 08 '20 at 14:23
  • Thanks, cant I use it like this in a function: @first_name_without_none_latin_chaaracters = Translate (@first_name,'Ææ','Aa') COLLATE Latin1_General_CS_AS Cause I need it in a function not just a select. – user1372603 Apr 08 '20 at 14:48
  • Then turn it into a function, @user1372603 . The solution you have is more than simple to make into a function if you must. Why do you need a scalar function to do this though? Unless you have SQL Server 2019 I recommend against that decision due to the lack of inlining. – Thom A Apr 08 '20 at 16:33