Skip site navigation (1) Skip section navigation (2)

"_" in a serach pattern

From: Jessica Richard <rjessil(at)yahoo(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: "_" in a serach pattern
Date: 2007-07-20 16:06:53
Message-ID: 102980.5844.qm@web56412.mail.re3.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-admin
I have a huge table that has a char(80) name column (indexed).

The name pattern is like 

A_B_C-D.123.456.pdf
A_B_C-D.123.333.doc.2

When I select name from table where name like 'A_B%', it was doing a table scan;

I guess the underscore "_" was treated like a special character, instead of a character "_".

When I use select name from table where name like 'A\\_B%', it was doing an index scan with my result returned very fast, but I got a warning about the \\:

WARNING:  nonstandard use of \\ in a string literal
LINE 1: ...elect name from table where file_name like 'A\\_B...
                                                             ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.

By the way, the wild card works if it is at the end of the query, it does not seem to work if it is in the middle:

select name from table where name like 'A\\_B%'  -- fine;

select name from table were name like 'A\\_B%.pdf'; -- returns nothing.

questions:

1. How do I get rid of the nonstandard warning, but still using the index search?

2. How do I search with a wild card % in the middle of the pattern? Would varchar(80) or char(80) make a difference about the wild card search (% in the middle)?

Thanks!

       
---------------------------------
Need a vacation? Get great deals to amazing places on Yahoo! Travel. 

Responses

pgsql-admin by date

Next:From: Mark StebenDate: 2007-07-20 18:19:36
Subject: row level locking
Previous:From: Michael FuhrDate: 2007-07-20 14:14:15
Subject: Re: Is there a way to run CREATE TABLESPACE within a stored procedure?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group