Re: how to escape _ in select

On Wed, Jul 28, 2010 at 12:47 PM, Justin Graf <justin(at)magwerks(dot)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