oracle - how to get value containing special symbol in where clause -

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

i wrote code below

select   value, name_display_code               vp40.attributes              attribute_type_id in (                       select attribute_type_id                         vp40.attribute_types                        name_display_code =                                    'attribute_type.r'                                 || '&'                                 || 'd gmd no'||'.') 

i need value attribute_type.r&d gmd no.

tl;dr: set define off before running query (probably).

---

a period (.) not special character. way you've split , concatenated value makes me wonder if you're seeing substitution variable issue in sql*plus or sql developer (or maybe other client) because of &, can in turn can make . seem disappear - though not in specific case.

a period can mark end of substitution variable, , needed if next character part of string, if had:

select 'r&ddept' dual; 

then entire 'ddept' treated substitution variable:

enter value ddept: d old   1: select 'r&ddept' dual new   1: select 'rd' dual  'r -- rd 

to make d substitution variable , leave 'dept' fixed string, delimit variable period:

select 'r&d.dept' dual;  enter value d: d old   1: select 'r&d.dept' dual new   1: select 'rddept' dual  'rddep ------ rddept 

but if want period displayed well, need add one, account 1 swallowed substitution processing:

select 'r&d..dept' dual;  enter value d: d old   1: select 'r&d..dept' dual new   1: select 'rd.dept' dual  'rd.dep ------- rd.dept 

obviously applies if want substitution, , of course & doesn't appear in final string in of cases. more confusingly (for me) in case, having space between & , . means not treated delimiter anyway; 'attribute_type.r&d gmd no.' &d treated substitution , . left is. i'm guessing got point shorter value.

what want achieve here avoid substitution altogether. way you're doing it, splitting string & seen on own , no substitution seen, 1 way painful, particularly if don't control string being used or there multiple values.

there @ least 2 other ways; set escape , set define:

set escape '\' select 'attribute_type.r\&d gmd no.' dual; 

this defines escape character sql*plus session, , can put character before ampersand - \& - not treated substitution variable. still have modify original string, isn't ideal.

set define off select 'attribute_type.r&d gmd no.' dual; 

this disables substitution altogether, original string remains intact. simplest approach generally. can toggle on , off needed in script; if have single statement needs substitution in places has ampersands want keep can either revert escape mechanism, or define different substitution character, long can find else you'll never need escape.

(you may see references set scan off; predates set define , is obsolete, don't use in new code).