Thread: The ignored "_" character

Laszlo Kohl (lkohl@matavnet.hu) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
The ignored "_" character

Long Description
Hi,

I am Laszlo from Hungary.
I have some problem with PostgreSQL.

Environment:
PostgreSQL version: 7.0.3
Server OS: RedHat 5.2 , Linux-Mandrake 7.2
Client OS: RedHat 5.2 , Windows NT (pgAdmin) , Linux-Mandrake 7.2

The problem:
I create some users in database and then want to list them.
The "_" character is ignored in query statement.
The query: "select usename from pg_user where usename Like '%_u%' ;"
produces the same result as the query:
"select usename from pg_user where usename Like '%u%' ;"



Sample Code
/* Create users... */
create user i_adminuser  with sysid 40 password 'adminuser' createdb createuser ;
create user i_login      with sysid 41 PASSWORD 'login';
create user i_lekerdezo1 with sysid 45 password 'lekerdezo1';
create user i_ugyintezo1 with sysid 46 password 'ugyintezo1' createuser ;

/* The query 1: */
select usename from pg_user where usename Like '%_u%' ;

/* The Result: */
   usename
--------------
 i_adminuser
 i_ugyintezo1
(2 rows)

/* The query 2: */
select usename from pg_user where usename Like '%u%' ;

/* The Result: */
   usename
--------------
 i_adminuser
 i_ugyintezo1
(2 rows)



No file was uploaded with this report

On Mon, May 07, 2001 at 03:01:28AM -0400, pgsql-bugs@postgresql.org wrote:
> Laszlo Kohl (lkohl@matavnet.hu) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> The ignored "_" character
>
> Long Description
> Hi,
>
> I am Laszlo from Hungary.
> I have some problem with PostgreSQL.
>
> Environment:
> PostgreSQL version: 7.0.3
> Server OS: RedHat 5.2 , Linux-Mandrake 7.2
> Client OS: RedHat 5.2 , Windows NT (pgAdmin) , Linux-Mandrake 7.2
>
> The problem:
> I create some users in database and then want to list them.
> The "_" character is ignored in query statement.
> The query: "select usename from pg_user where usename Like '%_u%' ;"
> produces the same result as the query:
> "select usename from pg_user where usename Like '%u%' ;"

 Sure it's not bug, please read something about the 'like' operator
first, en example:


http://postgresql.wavefire.com/users-lounge/docs/7.1/user/functions-matching.html


If pattern does not contain percent signs or underscore, then the pattern
only represents the string itself; in that case LIKE acts like the equals
operator. An underscore (_) in pattern stands for (matches) any single
character; a percent sign (%) matches any string of zero or more
characters.

 You must run:

 ... usename LIKE '%\\_u%'

 Note two '\' must be used here because PostgreSQL parser first remove
and 'like' operator obtain second only.

            Karel