Hi,
I have problems with Oracle8 and its special characters. Is it possible to set an escape character to handle those special characters and how can I do it?

<<< Jartsa >>> (Waiting...)

    What Oracle8 "special characters"? "%" and "_" are part of the SQL standards for query symbols. A colon ":" before bind variable names is a common notation.

    Puzzled,
    -- Michael
    Darkstreak Consulting
    www.darkstreak.com

      Problem was about escaping characters. If I want eg. insert a string that contains %,',_ or ", how can I do that?

      <<< Jartsa >>>

        If I want eg. insert a string that contains
        %,',_ or ", how can I do that?

        Inserting usually isn't the problem, selecting usually where most people encounter problems. When inserting, only the single-quote (') needs to be handled specially for Oracle. To insert a single-quote in a string, double it ('').

        CREATE TABLE yourtable (junk VARCHAR2(80));
        INSERT INTO yourtable VALUES ('99.999% uptime');
        INSERT INTO yourtable VALUES ('One_two_three');
        INSERT INTO yourtable VALUES ('This program outputs "Hello World!"');
        INSERT INTO yourtable VALUES ('Don''t worry about it.');
        COMMIT;
        SELECT * FROM yourtable;

        -- Michael
        Darkstreak Consulting
        www.darkstreak.com

          Thanks Michael, that helped me to solve insert related problems. Would you be so kind and give a little hint about how to deal with those characters in criteria clause.

          SELECT * FROM mytable
          WHERE junk LIKE '99.999%' ???

          UPDATE mytable SET junk='Something'
          WHERE junk LIKE 'This program outputs "Hello World!"' ???

          and so on...

          <<< Jartsa >>> (Feels kind of stupid...)

            You have nothing to feel stupid about. It takes time to learn new technologies. I've been doing SQL to Oracle for over 11 years now, so this comes naturally to me.

            Now on to the problem at hand. For writing WHERE clauses, here are a some quick rules to follow:

            1) Oracle uses single-quotes (') to delimit strings, so you need to double (example: 'O''Malley') any single-quotes inside your strings. With the exception noted below, all other characters are treated as normal characters.
            2) Strings in WHERE clauses are case sensitive (example: 'Foo' != 'FOO') by default.
            3) In non-LIKE clauses (example: a=b, a>b, a<b, a between b and c, etc...), the percent sign (%) and underbar () are treated like normal characters.
            4) In clauses that use the LIKE operator (example: a LIKE 'b%'), the percent sign (%) is handled as a wildcard for zero or more characters. Underbar (            ) matches any single character, including itself.
            5) CHR(37) and CHR(95) can be used in a LIKE clause to represent the percent sign (%) and underbar (_) characters respectively, without them having their wildcard effect.
            6) In SQLPlus, Oracle uses the ampersand (&) by default to denote DEFINE string subsituation. This only applies to SQLPlus, not to SQL sent to Oracle from PHP so you shouldn't have to worry about it. If you use SQL*Plus to test your SQL, then doing a "SET DEFINE |" or "SET DEFINE OFF" might sometimes prove useful.

            So, from your examples:
            1) SELECT * FROM mytable WHERE junk LIKE '99.999%'
            will return all rows from mytable where the column junk BEGINS WITH the characters
            "99.999". Examples of what junk could be would include:
            "99.999"
            "99.999%" (Here the % is just another character in the text)
            "99.999% uptime is attainable" (Here the % is just another character in the text)
            "99.999 bottles of beer on the wall"
            To retrieve ONLY the "99.999%", you could use any of the following:
            WHERE junk = '99.999%'
            WHERE junk = '99.999'||CHR(37)
            WHERE junk LIKE '99.999'||CHR(37) (equivalent the the "=" example above since there aren't any other wildcards in the string)
            I would not recommend "WHERE junk LIKE '99.999_'" since it would also return records with values like "99.9999" and "99.999X".

            2) UPDATE mytable SET junk='Something' WHERE junk LIKE 'This program outputs "Hello World!"'
            would update the junk column to be "Something" for all rows where junk is currently EXACTLY EQUAL to the string 'This program outputs "Hello World!"'. The reason that I stress the EXACTLY EQUAL is that since the search phrase doesn't contain a % or _, the LIKE is treated as an equal (=) operator.

            3) Examples of not-so-efficient (ie: usually doesn't allow indexes to be used) ways to search for a percent sign (%) anywhere inside a column would include:
            WHERE junk LIKE '%'||CHR(37)||'%'
            WHERE INSTR(junk, '%') > 0

            I hope this helps. If you need additional assistance, just let me know.
            -- Michael
            Darkstreak Consulting
            www.darkstreak.com

              2 months later

              Thank you Michael! I've been looking for that info about the single quote in a select now for quite a while. I really appreciate it. 🙂

                Write a Reply...