Re: Can the wildcard characters (_ %) used in "LIKE" clause be changed

In article <318BE81F.48F3_at_mail.concentric.net>, Ken Denny <kedenny_at_mail.concentric.net> wrote:

>I have an application where I allow users to search using wildcard characters.
>In my PL/SQL is do an INSTR(<search_value>,'%') and if it's not 0 I use a
>select with "WHERE <column> LIKE <search_value>" otherwise I use a select with
>a "WHERE <column> = <search_value>". I have no problem with the percent
>character (%) but the underscore character (_) occurs in the data and I would
>like for it to not be used as a wildcard. For instance I would like to be able
>to search for every row where columnx contains an underscore, but if I use
>"WHERE COLUMNX LIKE '%_%'" I get every one that contains at least one
>character. I there any way to tell Oracle to use a different single character
>replacement wildcard?
>
>Thanks
>Ken Denny

Ken,

There's no way I know of to switch the characters used, but the LIKE operator does support an escape character.

If you supplement you include the 'ESCAPE modifier to the LIKE comparator, you can use a clause such as "WHERE COLUMNX LIKE '%\_%' ESCAPE '\'".  In your case, you'll want to use a REPLACE( search_string,'_','\_' ) on the user's input 'search_string'. This way, all '_' will be escaped.

Hope it helps,

Kevin

-- 
-------------------------------------------------------------
Kevin Fries                         kelfink_at_ecst.csuchico.edu
CPD/PB, C Developer/DBA  http://www.ecst.csuchico.edu/~kelfink