Menu
I am trying to select data containing four percentage signs in a row. How can I escape the percentage signs so my LIKE condition works?
Thanks
729
Special characters can serve different functions in the query syntax. To search for a special character that has a special function in the query syntax, you must escape the special character by adding a backslash before it, for example: To search for the string "where?", escape the question mark as follows: "where\?"
Use braces to escape a string of characters or symbols. Everything within a set of braces in considered part of the escape sequence. When you use braces to escape a single character, the escaped character becomes a separate token in the query. Use the backslash character to escape a single character or symbol.
In ANSI SQL, the backslash character (\) is the escape character.
Escape sequences are used within an SQL statement to tell the driver that the escaped part of the SQL string should be handled differently. When the JDBC driver processes the escaped part of an SQL string, it translates that part of the string into SQL code that SQL Server understands.
Use @% with the escape character clause:
select *
from tbl
where fld like '%@%%' escape '@'
This will search for all records that contain the "%" character in the fld column.
DB2/z has a slightly different format:
select *
from tbl
where fld like {escape '@'} '%@%%'
Obviously, you'll need to choose your escape character carefully so it won't interfere with the rest of your string but this is relatively easy for static strings. Dynamically built strings will require dynamically built queries so that it doesn't use a character from the string.
109
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
