Message-ID: <4C507B3E.3000109@magwerks.com>
Date: Wed, 28 Jul 2010 13:47:26 -0500
From: Justin Graf <justin@magwerks.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.1.11) Gecko/20100711 Thunderbird/3.0.6
MIME-Version: 1.0
To: Wes James <comptekki@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: how to escape _ in select
References: <AANLkTi=7K_xTEQSFaOX3=K_YDiNjvDedjcfBSTRpO240@mail.gmail.com>
In-Reply-To: <AANLkTi=7K_xTEQSFaOX3=K_YDiNjvDedjcfBSTRpO240@mail.gmail.com>
Content-Type: multipart/mixed; boundary="------------030105030704020201080807"

This is a multi-part message in MIME format.
--------------030105030704020201080807
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit

On 7/28/2010 12:35 PM, Wes James wrote:
> I'm trying to do this:
>
> select * from table where field::text ilike '%\_%';
>
> but it doesn't work.
>
> How do you escape the _ and $ chars?
>
> The docs say to use \, but that isn't working.
>
> ( http://www.postgresql.org/docs/8.3/static/functions-matching.html )
>
> The text between '%...%' can be longer, I'm just trying to figure out
> how to escape some things.  I've found that ' works with '' and \
> works with \\
>    

Instead of escaping how about looking at double $ quoting.

http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html

4.1.2.4. Dollar-Quoted String Constants

While the standard syntax for specifying string constants is usually 
convenient, it can be difficult to understand when the desired string 
contains many single quotes or backslashes, since each of those must be 
doubled. To allow more readable queries in such situations, PostgreSQL 
provides another way, called "dollar quoting", to write string 
constants. A dollar-quoted string constant consists of a dollar sign 
($), an optional "tag" of zero or more characters, another dollar sign, 
an arbitrary sequence of characters that makes up the string content, a 
dollar sign, the same tag that began this dollar quote, and a dollar 
sign. For example, here are two different ways to specify the string 
"Dianne's horse" using dollar quoting:

$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$

Notice that inside the dollar-quoted string, single quotes can be used 
without needing to be escaped. Indeed, no characters inside a 
dollar-quoted string are ever escaped: the string content is always 
written literally. Backslashes are not special, and neither are dollar 
signs, unless they are part of a sequence matching the opening tag.

It is possible to nest dollar-quoted string constants by choosing 
different tags at each nesting level. This is most commonly used in 
writing function definitions. For example:

$function$
BEGIN
     RETURN ($1 ~ $q$[\t\r\n\v\\]$q$);
END;
$function$

Here, the sequence $q$[\t\r\n\v\\]$q$ represents a dollar-quoted literal 
string [\t\r\n\v\\], which will be recognized when the function body is 
executed by PostgreSQL. But since the sequence does not match the outer 
dollar quoting delimiter $function$, it is just some more characters 
within the constant so far as the outer string is concerned.

The tag, if any, of a dollar-quoted string follows the same rules as an 
unquoted identifier, except that it cannot contain a dollar sign. Tags 
are case sensitive, so $tag$String content$tag$ is correct, but 
$TAG$String content$tag$ is not.

A dollar-quoted string that follows a keyword or identifier must be 
separated from it by whitespace; otherwise the dollar quoting delimiter 
would be taken as part of the preceding identifier.

Dollar quoting is not part of the SQL standard, but it is often a more 
convenient way to write complicated string literals than the 
standard-compliant single quote syntax. It is particularly useful when 
representing string constants inside other constants, as is often needed 
in procedural function definitions. With single-quote syntax, each 
backslash in the above example would have to be written as four 
backslashes, which would be reduced to two backslashes in parsing the 
original string constant, and then to one when the inner string constant 
is re-parsed during function execution.


All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.

--------------030105030704020201080807
Content-Type: text/x-vcard; charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="justin.vcf"

begin:vcard
fn:Justin Graf
n:Graf;Justin
org:Magwerks Corp
adr:;;501 Commerce Drive;Danville ;IN;46122;USA
email;internet:justin@magwerks.com
tel;work:317-241-8011 ext 703
tel;fax:317-241-8015
x-mozilla-html:FALSE
url:www.magwerks.com
version:2.1
end:vcard


--------------030105030704020201080807--