sqlobject-discuss

[SQLObject] _likeQuote

[Oleg B. <ph...@ph...>]

Hello! Why double percent?

   I want to generate a query like

SELECT * FROM atable WHERE afield LIKE '%pattern\%pattern%';

   (I use SQLObject 0.5.3 with Postgres and MySQL, both use \% and \_ to
quote these special characters.).

CONTAINSSTRING(Atable.q.afiled, "pattern%pattern")

   produces

SELECT * FROM atable WHERE afield LIKE '%pattern%%pattern%';

   instead.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] _likeQuote

[Ian B. <ia...@co...>]

Oleg Broytmann wrote:
> Hello! Why double percent?

CONTAINSSTRING hides the underlying wildcard implementation, quoting % 
as %%.  You can use sqlbuilder.LIKE for what you're doing.

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org

Re: [SQLObject] _likeQuote

[Oleg B. <ph...@ph...>]

On Fri, Nov 05, 2004 at 02:41:55PM -0600, Ian Bicking wrote:
> Oleg Broytmann wrote:
> >Hello! Why double percent?
> 
> CONTAINSSTRING hides the underlying wildcard implementation, quoting % 
> as %%.

   Quoting?

> You can use sqlbuilder.LIKE for what you're doing.

   Thank you.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] _likeQuote

[Ian B. <ia...@co...>]

Oleg Broytmann wrote:
> On Fri, Nov 05, 2004 at 02:41:55PM -0600, Ian Bicking wrote:
> 
>>Oleg Broytmann wrote:
>>
>>>Hello! Why double percent?
>>
>>CONTAINSSTRING hides the underlying wildcard implementation, quoting % 
>>as %%.
> 
> 
>    Quoting?

If you want to search for all fields that contain the text "100%" 
(literally, not treating % as a wildcard) you would search for
field LIKE "%100%%%"

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org

Re: [SQLObject] _likeQuote

[Oleg B. <ph...@ph...>]

On Fri, Nov 05, 2004 at 03:06:18PM -0600, Ian Bicking wrote:
> >   Quoting?
> 
> If you want to search for all fields that contain the text "100%" 
> (literally, not treating % as a wildcard) you would search for

   That's exactly what I want...

> field LIKE "%100%%%"

   ...but as I have said in the first message, both Postgres and MySQL
use different quoting style. They use backslash to quote % and _:

   LIKE "%100\%%"

I have consulted documentation for Postgres 7.3 and MySQL 4.0 before
asking about double percent.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] _likeQuote

[Ian B. <ia...@co...>]

Oleg Broytmann wrote:
> On Fri, Nov 05, 2004 at 03:06:18PM -0600, Ian Bicking wrote:
> 
>>>  Quoting?
>>
>>If you want to search for all fields that contain the text "100%" 
>>(literally, not treating % as a wildcard) you would search for
> 
> 
>    That's exactly what I want...
> 
> 
>>field LIKE "%100%%%"
> 
> 
>    ...but as I have said in the first message, both Postgres and MySQL
> use different quoting style. They use backslash to quote % and _:
> 
>    LIKE "%100\%%"
> 
> I have consulted documentation for Postgres 7.3 and MySQL 4.0 before
> asking about double percent.

Oh, I missed that part.  I just committed a change to sqlbuilder.py that 
quotes in different ways depending on the database.  Then it really 
needs a unit test, and I'm not sure what the quoting rules are for all 
databases.

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org

Re: [SQLObject] _likeQuote

[Oleg B. <ph...@ph...>]

On Fri, Nov 05, 2004 at 03:38:48PM -0600, Ian Bicking wrote:
> >   ...but as I have said in the first message, both Postgres and MySQL
> >use different quoting style. They use backslash to quote % and _:
> >
> >   LIKE "%100\%%"
> >
> Oh, I missed that part.  I just committed a change to sqlbuilder.py that 
> quotes in different ways depending on the database.

   Thank you!

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.