When development, I used 'test_1%' to find 'test_123' in like. But in production environment its not working. Using 'escape '\'' is working. is there any setting needs to set in oracle? I want to use without escape '\''.
The SQL LIKE Operator The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
Answer: Oracle handles special characters with the ESCAPE clause, and the most common ESCAPE is for the wildcard percent sign (%), and the underscore (_).
If you are searching for an underscore then escape it with a '\' so you would search for' \_'. When matching SQL patterns, you can use these symbols as placeholders: % (percent) to substitute for zero or more characters, or _ (underscore) to substitute for one single character.
The default behaviour of LIKE and the other comparison operators, = etc is case-sensitive.
try this in SQL Developer:
SELECT * FROM TABLE1 WHERE NAME LIKE 'test\_1%' escape '\'
in sql plus:
set escape '\' SELECT * FROM TABLE1 WHERE NAME LIKE 'test\_1%';
The other answers using the ESCAPE '\'
didn't work for me, but I was able to overcome this issue by using a REPLACE function:
SELECT * FROM name_of_table WHERE REPLACE(description, '_', '~') LIKE 'testing~%';
In Oracle, you can also use ESCAPE
like this:
SELECT * FROM name_of_table WHERE description LIKE 'testing\_%' ESCAPE '\';
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