BUG #7835: The _ acts like a wildcard when used as '%_%'

On Tue, Jan 29, 2013 at 12:20 PM, <elliott.groszek@navy.mil> wrote:

The following bug has been logged on the website:

Bug reference: 7835
Logged by: Elliott Groszek
Email address: elliott.groszek@navy.mil
PostgreSQL version: 9.0.11
Operating system: Linux
Description:

Using the _ (underscore) in a wildcard query accesses values with - (dash)
as well. This results in unexpected behaviors when some data values contain
the underscore and some data values contain the dash.

i guess you are using a LIKE expression. And in like both % and _ are
wildcards, as documented in:
http://www.postgresql.org/docs/9.2/static/functions-matching.html#FUNCTIONS-LIKE
And AFAIU, mandated by SQL Standard

"An underscore (_) in pattern stands for (matches) any single
character; a percent sign (%) matches any sequence of zero or more
characters."

if you only want to show those that contains an underscore (supressing
its wildcard behaviour) you need to use a escape character:

col LIKE '%\_%'

--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs