Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 2E3EC9FB3DA for ; Fri, 20 Jul 2007 13:06:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 67492-09 for ; Fri, 20 Jul 2007 13:06:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web56412.mail.re3.yahoo.com (web56412.mail.re3.yahoo.com [216.252.111.91]) by postgresql.org (Postfix) with SMTP id 1A1F69FA36A for ; Fri, 20 Jul 2007 13:06:53 -0300 (ADT) Received: (qmail 5949 invoked by uid 60001); 20 Jul 2007 16:06:53 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=c/Y62bIo3gxcS87W4Asyo2A7IxmelRQJdXwKxoR9MQPTcXm7MKWKaO0NCSCkxcOlxDJxVobFZIsGGkiiqNiXcXF/VIvApuFdnss1RmznUJgMrFHb8nX6trKLOgaPdhw2rG5/XNZSGR5UnuVWbBJ68K+xMnXbo3PpOXsUCLMDNxM=; X-YMail-OSG: ieMzg.sVM1lM1hBnWgxOxub74Xvy9tLLIJQJwOu3abCxHzAUpWpDaR513GcVwpU.3FPh0olHl7Phjs56rb762AUATTSg3I5I9c_4flXossl1qxCLaldwAVeGtgFQk4Mj Received: from [198.119.134.32] by web56412.mail.re3.yahoo.com via HTTP; Fri, 20 Jul 2007 09:06:53 PDT Date: Fri, 20 Jul 2007 09:06:53 -0700 (PDT) From: Jessica Richard Subject: "_" in a serach pattern To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1873219323-1184947613=:5844" Content-Transfer-Encoding: 8bit Message-ID: <102980.5844.qm@web56412.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.345 tagged_above=0 required=5 tests=AWL=1.084, BAYES_20=-0.74, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200707/231 X-Sequence-Number: 26212 --0-1873219323-1184947613=:5844 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --0-1873219323-1184947613=:5844 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --0-1873219323-1184947613=:5844--