How do I search with a text variable containing apostrophes?

Question

Problem

When searching with texts containing apostrophes, no results are found.

Execution

DECLARE @cidade NVARCHAR(50) 

SELECT @cidade = cidade FROM tabela_A WHERE id = xv

SELECT campo FROM tabela_B WHERE cidade = @cidade

The above example returns the result of the table_A with the city WATER EYE OF THE MARRIED MAN, but when performing the search in the tabela_B it does not return me anything.

When performing the search "manually", informing cidade = "OLHO D'AGUA DO CASADO" the result is as expected.

Attempts

Assigning the city value to a variable, I tried using the functions QUOTENAME and REPLACE, but to no avail.

Answer 1

Marcelo, to assign OLHO D'AGUA DO CASADO to a variable, you can use some ways to define constant, of which I transcribe two:

-- código #1 -- apóstrofo como delimitador de constante
declare @cidade nvarchar(200);
set @cidade= N'OLHO D''AGUA DO CASADO';
go

Beware that in D'AGUA there are no quotes but two apostrophes.

Already "EYE OF WATER MARRIED", delimited by quotation marks, will only be recognized as a constant if QUOTED_IDENTIFIER is OFF.

-- código #2 -- aspas como delimitador de constante
set QUOTED_IDENTIFIER OFF;
declare @cidade nvarchar(200);
set @cidade= "OLHO D'AGUA DO CASADO";
go

---

The search in the tabela_B, from a variable, can then be done as follows:

-- código #3
declare @cidade nvarchar(200);
set @cidade= N'OLHO D''AGUA DO CASADO';

SELECT campo 
  from tabela_B
  where cidade = @cidade;

---

The apostrophe is a punctuation mark whose function is to indicate the deletion of letters in a word, such as Santa Bárbara d'Oeste (dthe West). This deletion is called elision. In computers we usually use a similar graphic symbol, which is the apex.
Apex: '
Apostrophe (opens and closes): Ġ'
Quotes (open and close): " "