Обсуждение: how to escape _ in select

Поиск
Список
Период
Сортировка

how to escape _ in select

От
Wes James
Дата:
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 \\

thx,

-wes


Re: how to escape _ in select

От
Justin Graf
Дата:
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
ourproprietary 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
informationproprietary 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
thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have
receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this
e-mailimmediately. 
Thank you.

Вложения

Re: how to escape _ in select

От
Wes James
Дата:
On Wed, Jul 28, 2010 at 12:47 PM, Justin Graf <justin@magwerks.com> wrote:
> 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.


I tried this, but it just returns a count for all the records:

select count(*) from table where field::text ilike '%' || $$_$$ || '%';

.... ilike '%$$_$$%'

returns a count of 0

So does $$a$$ and I know there is some text in the field with an "a".

-wes


Re: how to escape _ in select

От
Tom Lane
Дата:
Wes James <comptekki@gmail.com> writes:
> I'm trying to do this:
> select * from table where field::text ilike '%\_%';

> but it doesn't work.

You need to double the backslash, because one level of
backslash-escaping will be eaten by the string literal parser.
In the above example, the actual string value seen by ILIKE
is just %_%, so of course it doesn't do what you want.
        regards, tom lane


Re: how to escape _ in select

От
Wes James
Дата:
Thanks Douglas and Tom - I missed that second \.

-wes


Re: how to escape _ in select

От
Wes James
Дата:
On Wed, Jul 28, 2010 at 12:15 PM, Little, Douglas
<DOUGLAS.LITTLE@orbitz.com> wrote:
> Wes.
>
> You probably missed the part in bold.   You need to double the backslash.
>
> select 'ab5c' like '%\_c'
>
> t
>

Why doesn't this work?

select * from table where field::text ilike '%\\\%'

WARNING:  nonstandard use of \\ in a string literal

-wes


Re: how to escape _ in select

От
Dmitriy Igrishin
Дата:
Hey James,

Because in future releases the default value of the "standard_conforming_strings"
parameter will change to "on" for improved standards compliance.
You should use string constants with C-Style escapes.
Please, see
http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
for details.

Regards,
Dmitriy

2010/7/29 Wes James <comptekki@gmail.com>
On Wed, Jul 28, 2010 at 12:15 PM, Little, Douglas
<DOUGLAS.LITTLE@orbitz.com> wrote:
> Wes.
>
> You probably missed the part in bold.   You need to double the backslash.
>
> select 'ab5c' like '%\_c'
>
> t
>

Why doesn't this work?

select * from table where field::text ilike '%\\\%'

WARNING:  nonstandard use of \\ in a string literal

-wes

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: how to escape _ in select

От
Thomas Kellerer
Дата:
Wes James, 28.07.2010 19:35:
> 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 \\
>

To get around the somewhat quirky usage of backslashes, you can simply define a different esacpe character:

select *
from table
where field::text ilike '%@_%' escape '@';

Thomas