Escaping Ampersand Character in SQL String

Escaping ampersand character in SQL string

Instead of

node_name = 'Geometric Vectors \& Matrices'

use

node_name = 'Geometric Vectors ' || chr(38) || ' Matrices' 

38 is the ascii code for ampersand, and in this form it will be interpreted as a string, nothing else. I tried it and it worked.

Another way could be using LIKE and an underline instead the '&' character:

node_name LIKE 'Geometric Vectors _ Matrices' 

The chance that you'll find some other record too, which is different in only this one character, is quite low.

Oracle SQL - Escape ampersand in field name

The substitution is related to tool you are using and has nothing to do with column alias.

db<>fiddle demo

---

Depending on the tool you could disable it like "set define off".

Related: Set define off not working in Oracle SQL Developer & How to escape ampersand in TOAD?

Oracle SQL escape character (for a '&')

the & is the default value for DEFINE, which allows you to use substitution variables. I like to turn it off using

SET DEFINE OFF

then you won't have to worry about escaping or CHR(38).

Escaping an ampersand in SQL Server Full-Text Search query using CONTAINSTABLE

The odd behavior you are seeing is most likely due to the CONTAINS and CONTAINSTABLE functions (both used with SQL Server's Full Text Search feature) using the ampersand ( & ) character as equivalent to the AND operator. The following statement is taken from the documentation for CONTAINS:

The ampersand symbol (&) may be used instead of the AND keyword to represent the AND operator.

There is no mention of there being any escape character for it (and a back-slash isn't typically an escape character in SQL anyway).

---

UPDATE

Based on the information now provided in "Edit 2" of the Question, and additional research, I would say that you do not need to escape anything. It seems that putting the search phrases in double-quotes (as a result of using FORMSOF) treats the & as either a literal or a word-breaker, depending on the values on both sides of the &. Try the following examples:

DECLARE @Term NVARCHAR(100);

SET @Term = N'bob&sally'; -- 48 rows
--SET @Term = N'bob\&sally'; -- 48 rows
--SET @Term = N'r&f'; -- 4 rows
--SET @Term = N'r\&f'; -- 24 rows

SET @Term = N'FORMSOF(INFLECTIONAL,"' + @Term + '")';

SELECT * FROM sys.dm_fts_parser(@Term, 1033, 0, 0);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, 0, 1);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, NULL, 0);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, NULL, 1);

The results for bob&sally and bob\&sally are the same, and in both cases bob and sally are separated and never combined into a single exact-match string.

The results between r&f and r\&f, however, are not the same. r&f is only ever treated as a single, exact-match string because r and f alone are not known words. On the other hand, adding in the back-slash separates the two letter since \ is a word-breaker, in which case you get both r and f.

Given that you stated in the Update that you have "data inconsistency, where one of the brands with the "&" in its name was modified not to have the "&", and the other one wasn't", I suspect that when you do not add in the \ character you get the brand that was not modified (since it is an exact match for the full term). But when you do add in the \ character, then you get the brand that was modified to have the & removed, since you are now searching on both pieces, each one matching part of that brand name.

I would fix the data to be consistent: update the brand names that had the & removed to put the ampersands back in. Then when people search using & without the extra \ added, it will be an exact match. This behavior will be consisted across the data, and will not require you adding code to circumvent the natural operation of FTS, which seems to be an error-prone approach.

Escape ampersand in PL/SQL Developer

How I solved it is escaping the & with another &.

For example:

INSERT INTO Foo (Bar) VALUES ('Up && Away');

Works nicely. Thanks for all the help

How to escape ampersand in TOAD?

Try putting set define off at the beginning of your script. It should work with F5:

set define off;
insert into x values('hello & world'); 
set define on;

---

Related Topics