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> writes:
|> Hello,
|>
|> 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?

I don't think that you can use a different character, but you can escape the underscore in the string using the ESCAPE clause. For example:

SELECT ...
  WHERE column LIKE '%\_%' ESCAPE '\'

should return only those rows containing an underscore. For more information, see the SQL Language Reference.

|>
|> Thanks
|> Ken Denny
|> --
|>
|> /| /
|> / | / _ _
|> |< / \ /|/ \
|> | \ \ / / | \
|> | \__X___/ | \___
 

-- 
------------------------------------------------------------------------
Scott Urman            Oracle Corporation           surman_at_us.oracle.com
------------------------------------------------------------------------
Author of "Oracle PL/SQL Programming", ISBN 0-07-882176-2
Published by Oracle Press - http://www.osborne.com/oracle/index.htm
------------------------------------------------------------------------
"The opinions expressed here are my own, and are not necessarily that of
 Oracle Corporation"
------------------------------------------------------------------------