How to search with a text variable containing apostrophes?

Question

How to search with a text variable containing apostrophes?

Navigation

#1 by (1 votes)

1

Problem

When searching for 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 table_A with the city OLHO D'AGUA DO AGUA , but when doing the search in table_B it does not return anything to me.

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 to use the QUOTENAME and REPLACE functions, but without success.

sql sql-server

asked by anonymous 21.10.2016 / 22:04

1 answer

Carrot does not show photo Pause an AsyncTask until a task is completed

score 1 · Accepted Answer

Marcelo, to assign OLHO D'AGUA DO CASADO to a variable, you can use some ways to set constants , among 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

Note that in 'D'AGUA there is not a quotation mark but two apostrophes.

"EYE OF AGE OF MARRIAGE", delimited by quotation marks, will only be recognized as 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 table_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 (d o Oeste). This suppression is called elision . In computers we usually use a similar graphic symbol, which is the apex. Apex: '
Apostrophe (open and date): ''
Quotes (open and closed): ""