This discussion is archived
6 Replies Latest reply: Dec 4, 2012 1:40 AM by 930854 RSS

escape sequence

930854 Newbie
Currently Being Moderated
Hi ,

Is it possible to use multiple special characters using ESCAPE opeartors.

eg:

select * from mak

NAME
ma%k
&kad
s$am

I want to display all the records in single select query. Is it possible? Please advice.
  • 1. Re: escape sequence
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    That is a rendering issue - how to display/render the data received from the server.

    This is therefore a client issue and not a server-side issue and not relevant to the server's SQL language. Despite the suggestions you will receive about string aggregation - which will be WRONG in this case.
  • 2. Re: escape sequence
    Purvesh K Guru
    Currently Being Moderated
    927851 wrote:
    Hi ,

    Is it possible to use multiple special characters using ESCAPE opeartors.

    eg:

    select * from mak

    NAME
    ma%k
    &kad
    s$am

    I want to display all the records in single select query. Is it possible? Please advice.
    But there does not seem like any use of escape characters in the query you posted. You are you trying to achieve. Can you be clearer on your requirements?

    I will also suggest you to read {message:id=9360002} and re-post the question will all mentioned information.
  • 3. Re: escape sequence
    930854 Newbie
    Currently Being Moderated
    Hi,

    sorry for the late reply

    my requirement is, suppose i need to give any special character in the application given specified column means, i need to retrieve those special character data records.

    Am using this query

    SELECT * FROM mak WHERE regexp_like (name,'%|&|$');

    but i gave all the special characters in the query. it is not possible to give all the special characters whatever oracle used. I want the generic query to display the particular records.

    Please advice.
  • 4. Re: escape sequence
    ranit B Expert
    Currently Being Moderated
    Try this...
    REGEXP_LIKE(name,'[^ -~]')
    This range of characters is the set of Non-Keyboard (non-printable) characters.

    It starts from a Space character (ASCII = 32) up to Tilde (ASCII = 126)

    >
    SELECT * FROM mak WHERE regexp_like (name,'%|&|$');
    >
    Could you please tell what all do you consider as Special Characters ?
    Depending on which the REGEXP might change.

    Hope this Helps.
    Ranit B.

    Edited by: ranit B on Dec 4, 2012 12:47 PM
  • 5. Re: escape sequence
    Purvesh K Guru
    Currently Being Moderated
    Something this way
    set define on;
    set escape on '\';
    
    with data as
    (
      select 'some_data' col from dual union all
      select 'some ^ data' col from dual union all
      select 'some % data' col from dual union all
      select 'some \& data' col from dual union all
      select 'some!data' from dual
    ),
    inp_chr as
    (
      select '&i' inp from dual
    )
    select *
      from data, inp_chr
     where instr(col, inp) > 0;
  • 6. Re: escape sequence
    930854 Newbie
    Currently Being Moderated
    got answer thank you reply

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points