Message-ID: <42354847.9070403@archonet.com>
Date: Mon, 14 Mar 2005 08:16:07 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Her Goo <gu_he@msn.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: sql: "LIKE" problem
References: <BAY11-DAV4899E308FCFAA7E626003F85560@phx.gbl>
In-Reply-To: <BAY11-DAV4899E308FCFAA7E626003F85560@phx.gbl>
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: 7bit

Her Goo wrote:
> I am using "postgresql-7.3.2-1" now!
> 
> I met a problem when using "LIKE" in "WHERE" clause.
> For example, a table named "t_test", and its data looks like below:
> 
> # SELECT * FROM t_test;
>  id | c_name 
> ----+--------
>   1 | abc\
>   2 | abc\de
> (2 rows)

> # SELECT * FROM t_test WHERE c_name LIKE 'abc\d%';
>  id | c_name 
> ----+--------
> (0 rows)
> 
> I don't know why the result is "0 rows"(why not is "1 rows"), 
> And I have trid "LIKE 'abc\\d%'", the result is also "0 rows".

You'll need to escape the backslash twice. Once for the SQL-level
parsing and once for the LIKE pattern-matching itself.

SELECT * FROM t_test WHERE c_name LIKE 'abc\\\\d%';

--
  Richard Huxton
  Archonet Ltd