Msg#: 3543936 posted 5:41 am on Jan 11, 2008 (gmt 0)
I believe the following will work in both Oracle and SQL*Server:
SELECT * FROM products WHERE description LIKE '%\%%' ESCAPE '\'
The predicate "ESCAPE '\'" tells Oracle that you are using the '\' in the like predicate to 'escape' special characters. Normally a % sign in a string following LIKE has a special meaning - it is a wildcard. But in this case the 2nd '%' is escaped meaning take it as a literal.
So the above statement should return all rows from products where the description contains any number of characters (1st % is a wildcard) followed by at least one '%' character (2nd % is an escaped literal) followed by any number of characters (3rd % is another wildcard).
Msg#: 3543936 posted 3:34 pm on Jan 11, 2008 (gmt 0)
Do you really want to enter a literal percent-sign in a column?
Or do you want a column to represent a percentage value?
If the former, I assume you are using some kind of text data type. You will have to check the specific rules for escaping characters for your SQL if % is a special character inside of a string. But I don't know of any SQL system where % is a special character in a string. Just make sure you put quotation marks around the string.
I suspect, though, that you want a column to represent a percentage value. In most (if not all) SQL systems, there's no specific data type for percentages.
Just use an integer or float data type. You will need to add the percentage sign on output, remove it on input, and scale the value (divide by 100) when using it as a multiplier.