Escape special character in a LIKE clauseTag(s): JDBC


Your JDBC driver may support the {escape 'escape character'} syntax for using LIKE clause wildcards as literals. The escape sequence must be at the end of the SQL statement.

Searching for "one_word"

st = con.createStatement();
rs = st.executeQuery
 ("SELECT value FROM vendors WHERE value LIKE 'one/_word' {escape '/'}");

Searching for strings ending with "one%word"

st = con.createStatement();
rs = st.executeQuery
 ("SELECT value FROM vendors WHERE value LIKE '%one/%word' {escape '/'} ");

Find all rows in which a begins with the character "%"

st = con.createStatement();
rs = st.executeQuery
 ("SELECT value FROM vendors WHERE value LIKE '$%%' {escape '$'}");

Find all rows in which a ends with the character "_"

st = con.createStatement();
rs = st.executeQuery
 ("SELECT value FROM vendors WHERE value LIKE '%=_' {escape '='}");

If you don't want to use JDBC escape sequence then it's possible to use native SQL based on the target DBMS.

For Oracle, it will look like this :

SELECT value FROM vendors WHERE value LIKE '%=_%' ESCAPE '=';
ref : Labo-Oracle.com.
blog comments powered by Disqus