pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsql: "LIKE" problem
3+ messages / 3 participants
[nested] [flat]
* sql: "LIKE" problem
@ 2005-03-14 06:16 Her Goo <gu_he@msn.com>
0 siblings, 2 replies; 3+ messages in thread
From: Her Goo @ 2005-03-14 06:16 UTC (permalink / raw)
To: pgsql-sql
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)
The thing I want to do is searching in above table and restricting
the results to those that begin with string "abc\". The search result is:
# SELECT * FROM t_test WHERE c_name LIKE 'abc\%';
id | c_name
----+--------
1 | abc\
2 | abc\de
(2 rows)
I want to go a step further, and restrict the results to those
that begin with string "abc\d". The search result is:
# 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".
Is this a bug for "postgresql-7.3.2-1" ?
Or how can I search a field when it contains a backslash('\') ?
Thanks!!!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: sql: "LIKE" problem
@ 2005-03-14 07:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Her Goo <gu_he@msn.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-03-14 07:32 UTC (permalink / raw)
To: Her Goo <gu_he@msn.com>; +Cc: pgsql-sql
"Her Goo" <gu_he@msn.com> writes:
> I want to go a step further, and restrict the results to those
> that begin with string "abc\d".
You need four backslashes for that, because \ is special to both
the string literal parser and the LIKE operator. So '\\\\'
reduces to a string constant containing \\, and then the LIKE
operator sees that as a quoted backslash.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: sql: "LIKE" problem
@ 2005-03-14 08:16 Richard Huxton <dev@archonet.com>
parent: Her Goo <gu_he@msn.com>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2005-03-14 08:16 UTC (permalink / raw)
To: Her Goo <gu_he@msn.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-03-14 08:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-14 06:16 sql: "LIKE" problem Her Goo <gu_he@msn.com>
2005-03-14 07:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-14 08:16 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox