Matching "&" in oracle

Posted on 2004-10-27
Last Modified: 2010-10-05
I have a column "A" in a table "T" which has a data with special characters like &.
For example lets say the values is P&L
Now if I write a query
select * from T where A = 'P&L';
It prompts me for the value of L, instead of matching the record. I know that this is because of & being a special character. I tried negating the special character using \ and {} , it doesnt work.
Also I do not want to use like '%&%' my where clause.

Question by:sathyagiri
    LVL 25

    Expert Comment


    where A = 'P' || '&' || 'L'
    LVL 23

    Accepted Solution

    set define off works fine
    LVL 23

    Expert Comment

    Just an example:

    SQL> set define off
    SQL> select '&' "&" from dual;


    LVL 12

    Assisted Solution

    In sql issue the command:

    sql> set escape off
    sql> select * from T where A = 'P\&L';

    Answer to a similar question :
    LVL 14

    Author Comment

    Thanks a lot both set define off and set escape off works fine.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    This article will show you how to install Oracle Database 10g Express Edition step by step and unlocking 'HR' user which is provided by Oracle by default once we install 10g Express Edition. For installing Oracle 10g express edition we need to g…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now