1

I've seen a bunch of related posts, but none yet that resolve my specific question.

In Oracle SQL I need to do something like this:

SELECT field1 "Eggs&Cheese"
FROM table1;

But it reads the &Cheese and wants to do parameter substitution. I just want the field name to be Eggs&Cheese

I saw this post Escape ampersand with SQL Server, but Oracle does not like the bracket [] syntax.

And also Escaping ampersand character in SQL string, but that is escaping the ampersand in a value string, not a label string.

alexherm
  • 1,362
  • 2
  • 18
  • 31

2 Answers2

4

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?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • I am using Toad for Oracle. ```SET DEFINE OFF``` does not resolve. Any idea how to get around it in Toad? – alexherm Nov 08 '19 at 20:14
  • 3
    @alexherm https://stackoverflow.com/a/52402623/5070879 – Lukasz Szozda Nov 08 '19 at 20:15
  • 1
    Yes. Works! Thank you. Tricky little Toad. ```In TOAD version 12.120.39 Right click in Editor and Click in Substitution Variable Prompting and select none``` – alexherm Nov 08 '19 at 20:17
0

You have to set the escape. Works in Oracle SQL Developer.

set escape \
SELECT field1 "Eggs\&Cheese" FROM table1;

After your work is done you can set it off.

set escape off
SK -
  • 459
  • 5
  • 15