pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jessica Richard <rjessil@yahoo.com>
To: pgsql-admin@postgresql.org
Subject: "_" in a serach  pattern
Date: Fri, 20 Jul 2007 09:06:53 -0700 (PDT)
Message-ID: <102980.5844.qm@web56412.mail.re3.yahoo.com> (raw)

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. 

view thread (7+ messages)  latest in thread

Message-ID: <102980.5844.qm@web56412.mail.re3.yahoo.com>
Permalink:  ../102980.5844.qm@web56412.mail.re3.yahoo.com/
Also on:    postgresql.org/message-id/102980.5844.qm@web56412.mail.re3.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: rjessil@yahoo.com
  Subject: Re: "_" in a serach  pattern
  In-Reply-To: <102980.5844.qm@web56412.mail.re3.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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