Escape & symbol with space in SQL

Question

I am struck with following query. Please help me on this.

select 
      case when column in ('1 Chelsea'|| chr(38) ||
      'Friend (2)','5 Manchester''s Song'||chr(38)||
      'Hamilton') then 'Others' else N/A
from FA

Output of above query

1 Chelsea & Friend (2)          N/A
5 Manchester's Song&Hamilton   Others

From the above query the output should be like the following output

1 Chelsea & Friend (2)          Others
5 Manchester's Song&Hamilton   Others

Please someone help me on this. Also note that the I dont have access to use SET DEFINE OFF.

*I dont have access to use SET DEFINE OFF* What do you mean you don't have access? If you can execute the SQL, then why not `SET DEFINE OFF`? — Lalit Kumar B, May 23 '15 at 16:41

score 1 · Answer 1 · answered May 22 '15 at 10:49

1

A ' in an SQL string has to be escaped with another '. So 'my ''house''' means my 'house' actually.

Hence '5 Manchester''s Song' doesnt match 5 Manchester''s Song, but 5 Manchester's Song. You must query for '5 Manchester''''s Song' instead.

BTW: It is not necessary to use chr(38), you could use & directly:

select 
  case when column in (
    '1 Chelsea & Friend (2)',
    '5 Manchester''''s Song&Hamilton'
  ) then 'Others' else 'N/A' end
from FA;

answered May 22 '15 at 10:49

Thorsten Kettner

89,309
7
49
73

I prefer Quoting string literal technique. – Lalit Kumar B May 22 '15 at 11:04
*BTW: It is not necessary to use chr(38), you could use & directly:* No, it will prompt for substitution. – Lalit Kumar B May 22 '15 at 11:07
@Lalit Kumar B: Okay, then this is something special in SQL Developer. A strange (and probably annoying) feature. Does that tool have no setting to switch it off? – Thorsten Kettner May 22 '15 at 11:15
Most of the GUI based client tools are capable of executing the SQL*Plus commands. `SET DEFINE OFF` would work fine. – Lalit Kumar B May 22 '15 at 11:32
I have updated correct output of query now. Apologies – Raaj May 23 '15 at 11:10

score 0 · Answer 2 · edited May 23 '17 at 12:14

I would suggest two things:

SET DEFINE OFF or, CHR(38) to get rid off the substitution operator &

Most of the GUI based client tools are capable of executing the SQL*Plus commands. Execute it once in the session or SQL Worksheet in SQL Developer, and it won't prompt you again to enter a substitution value:

SET DEFINE OFF

Better use the Quoting string literal technique

'5 Manchester''''s Song'

Using quoting string literal technique, it could just be written as:

q'[Manchester's Song&Hamilton]'

For example,

SQL> SET DEFINE OFF
SQL> WITH DATA(a, b) AS(
  2  SELECT 1,  'Chelsea & Friend (2)' FROM dual UNION ALL
  3  SELECT 5, q'[Manchester's Song&Hamilton]' FROM DUAL UNION ALL
  4  SELECT 6, 'abcd' FROM DUAL
  5  )
  6  SELECT a,
  7    CASE
  8      WHEN b IN ('Chelsea & Friend (2)',q'[Manchester's Song&Hamilton]')
  9      THEN 'Others'
 10      ELSE 'N/A'
 11    END status
 12  FROM DATA
 13  /

         A STATUS
---------- ------
         1 Others
         5 Others
         6 N/A

SQL>

Regarding a more detailed example on the quoting string literal technique see this answer How to anticipate and escape single quote ' in oracle

I have updated correct output of query now. Apologies – Raaj May 23 '15 at 11:10 — Raaj, May 23 '15 at 11:10

Escape & symbol with space in SQL

2 Answers2