6 Replies Latest reply: Dec 4, 2012 3:40 AM by 930854 RSS

    escape sequence

    930854
      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
          Billy~Verreynne
          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
            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
              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
                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
                  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
                    got answer thank you reply