How to convert French Accent into Latin1 in SQL Server

I encounter an issue today, when i was asked to extract data and sent them to third party for further process.








those highlight French characters will cause an issue when i extract data and save in CSV format.

the client suggest that they can process the file by scrub the data with Latin1 Encode.

we can solve this issue by select the data with COLLATE in SQL Server to encode the data in Latin1 with  COLLATE SQL_Latin1_General_Cp1251_CS_AS.



SELECT
      ,Convert( varchar(100), ltrim(rtrim([MailAddress1]))) COLLATE SQL_Latin1_General_Cp1251_CS_AS as MailAddress1
      --,ltrim(rtrim([MailAddress1])) as MailAddress1
      ,ltrim(rtrim([MailAddress2])) as MailAddress2
      --,ltrim(rtrim([MailCity])) as MailCity
      ,Convert( varchar(50), ltrim(rtrim([MailCity]))) COLLATE SQL_Latin1_General_Cp1251_CS_AS as MailCity
      ,ltrim(rtrim([MailProvince])) as MailProvince
      ,[MailPostCode]
      ,[Phone]
  FROM ShippingCustomer


the outcome will convert those french accent to english