CODEWITHSUNDEEP
oracle-database
ravt
ravt

Reputation: 71

how to get value containing special symbol in where clause

i have a requirement to pull the column value containing special symbol('.').

i wrote the code like below

SELECT   value, name_display_code
              FROM vp40.ATTRIBUTES
             WHERE attribute_type_id IN (
                      SELECT attribute_type_id
                        FROM vp40.attribute_types
                       WHERE name_display_code =
                                   'ATTRIBUTE_TYPE.R'
                                || '&'
                                || 'D GMD NO'||'.')

i need value is ATTRIBUTE_TYPE.R&D GMD NO.

Upvotes: 0

Views: 4089

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

TL;DR: set define off before running your query (probably).

A period (.) is not a special character. The way you've split up and concatenated the value makes me wonder if you're actually seeing a substitution variable issue in SQL*Plus or SQL Developer (or maybe other another client) because of the &, which can in turn can make a . seem to disappear - though not in your specific case.

A period can mark the end of a substitution variable, and is needed if the next character is part of the string, so if you had:

select 'R&Ddept' from dual;

then the entire 'Ddept' would be treated as the substitution variable:

Enter value for ddept: D
old   1: select 'R&Ddept' from dual
new   1: select 'RD' from dual

'R
--
RD

To make just the D be the substitution variable and leave 'dept' as a fixed string, you delimit the variable with a period:

select 'R&D.dept' from dual;

Enter value for d: D
old   1: select 'R&D.dept' from dual
new   1: select 'RDdept' from dual

'RDDEP
------
RDdept

But if you want a period to actually be displayed as well, you need to add an extra one, to account for the one swallowed by the substitution processing:

select 'R&D..dept' from dual;

Enter value for d: D
old   1: select 'R&D..dept' from dual
new   1: select 'RD.dept' from dual

'RD.DEP
-------
RD.dept

Obviously this only applies if you actually want the substitution, and of course the & doesn't appear in the final string in any of those cases. More confusingly (for me) in your case, having a space between the & and the . means it is not treated as a delimiter anyway; for 'ATTRIBUTE_TYPE.R&D GMD NO.' only the &D is treated as a substitution and the . is left as it is. I'm guessing you got to this point with a shorter value.

What you probably want to achieve here is to avoid the substitution altogether. The way you're doing it, splitting up the string so the & is seen on its own and no substitution is seen, is certainly one way but painful, particularly if you don't necessarily control the string being used or there are multiple values.

There are at least two other ways; with set escape and set define:

set escape '\'
select 'ATTRIBUTE_TYPE.R\&D GMD NO.' from dual;

This defines an escape character for the SQL*Plus session, and you can then put that character before the ampersand - \& - so it is not treated as a substitution variable. But you still have to modify your original string, which isn't ideal.

set define off
select 'ATTRIBUTE_TYPE.R&D GMD NO.' from dual;

This disables substitution altogether, so the original string remains intact. This is the simplest approach generally. You can toggle it on and off as needed in your script; if you have a single statement that needs substitution in some places but also has ampersands that you want to keep you can either revert to the escape mechanism, or define a different substitution character, as long as you can find something else you'll never need to escape.

(You may also see references to set scan off; this predates set define and is obsolete, so don't use that in new code).

Upvotes: 3

Related Questions