Thread: sql: "LIKE" problem

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!!!

"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

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