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
