C# and MS SQL Server Special Characters

Question

I have a table of municipalities within the system, MS SQL Server database, and the names of municipalities are accented.

I am consuming an API where municipalities come without any accentuation, so select does not work because of these differences.

I created a select with MSSQL replace function and when I run directly by MSSQL query select works.

select I put inside the application

SELECT OCNT."AbsId"
FROM OCNT
WHERE
REPLACE(OCNT."NAME",'ã','a') = 'Sao Paulo'

If I use this same select in the application the same does not work, I turned on the MSSQL profile and captured the select, it changes the select and I honestly do not know the reason that it makes the exchange.

Below the select I took from the MSSQL profile

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,NULL,N'SELECT OCNT."AbsId"
, [OCNT].AbsId AS ''kEY_AbsId'' 
FROM OCNT
WHERE
REPLACE(OCNT."NAME",''ã'',''a'') = ''SAO PAULO''
 FOR BROWSE '
select @p1

In no way I can give UPDATE in the database to take the characters because it is a proprietary system, which limits me to create views, functions and stored procedures.

I wonder if anyone’s been through it and how to get around it.

Thank you

Answer 1

You don’t have to do REPLACE explicit, the SQL SERVER has the leotard that can do this directly in the query result. It is a statement that can be used in the command SELECT, thus:

SELECT OCNT."AbsId"
  FROM OCNT
 WHERE OCNT."NAME" = 'Sao Paulo' COLLATE Latin1_general_CI_AI;

More information on COLLATE, Unicode or other types: https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15

See an example here working: http://sqlfiddle.com/#! 18/b3bf7d/1