Thread: LIKE wildcards escaping problem
Hello folks :)
I am working on a system that is possible to configure to work with
several DBMSes, including PG.
My problem is that PG behaves differently than other supported DBMSes
(MSSQL and MySQL) - when I'm passing a query containing LIKE phrase to
it, a double amount of '/' literals is needed to obtain expected
result. I do realize that this is caused by a parser 'collapsing'
double '/' to a single one.
I might not express this clearly, but - as I understand it - in
general result is that to find '/' literal in DB, '////' phrase is
needed ('////' is turned to '//' by a parser, and that is valid
expression for '/' literal in SQL itself).
The problem is that I cannot really use ESCAPE clause - it would
require working on too many files. (there are about 6k files in this
project, changing most of them would be not only not feasible, but
also risky)
The problem is that there is a table with user names. Those can
contain '/' literal (if they are domain users for example.) And here I
hit the wall. Any search for username with '/' literal fails.
I've searched through postgres mailing lists and all feed I could
google out, and found some info about problem itself, but I was unable
to find a solution for it anywhere.
Problem was addressed in following locations:
http://svr5.postgresql.org/pgsql-general/1999-07/msg00340.php
http://svr5.postgresql.org/pgsql-sql/1999-03/msg00144.php
But those posts are from 1999, and I did not find anything more up-to-
date.
Has anyone solved this problem?
Is there any configuration option in PG allowing to make it behave in
a more compliant way?
I'd be more than happy to see something like Herouth Maoz suggested
in
http://svr5.postgresql.org/pgsql-general/1999-07/msg00340.php , namely
configuration option for PG, but I was unable to find it.
Sorry if I messed something up... I'm really very confused with this
sftuff...
I will appreciate any help or explanation, if I missed something
important.
Thea wrote:
> My problem is that PG behaves differently than other supported DBMSes
> (MSSQL and MySQL) - when I'm passing a query containing LIKE phrase to
> it, a double amount of '/' literals is needed to obtain expected
> result. I do realize that this is caused by a parser 'collapsing'
> double '/' to a single one.
> I might not express this clearly, but - as I understand it - in
> general result is that to find '/' literal in DB, '////' phrase is
> needed ('////' is turned to '//' by a parser, and that is valid
> expression for '/' literal in SQL itself).
You must be meaning \, the backslash character, not /. The setting
you're looking for is "standard_conforming_strings = on".
BTW: This question would've been more suited for the pgsql-general list,
pgsql-hackers is for discussing development of Postgres.
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
This is the wrong list to ask this question. This list is about
development, not usage. Please ask on -general in future.
I presume that where you have "/" you really mean "\".
What version of postgres are you using? If you use a modern version with
standard_conforming_strings on then you do not need to escape backslashes.
cheers
andrew
Thea wrote:
> Hello folks :)
>
> I am working on a system that is possible to configure to work with
> several DBMSes, including PG.
>
> My problem is that PG behaves differently than other supported DBMSes
> (MSSQL and MySQL) - when I'm passing a query containing LIKE phrase to
> it, a double amount of '/' literals is needed to obtain expected
> result. I do realize that this is caused by a parser 'collapsing'
> double '/' to a single one.
> I might not express this clearly, but - as I understand it - in
> general result is that to find '/' literal in DB, '////' phrase is
> needed ('////' is turned to '//' by a parser, and that is valid
> expression for '/' literal in SQL itself).
>
> The problem is that I cannot really use ESCAPE clause - it would
> require working on too many files. (there are about 6k files in this
> project, changing most of them would be not only not feasible, but
> also risky)
> The problem is that there is a table with user names. Those can
> contain '/' literal (if they are domain users for example.) And here I
> hit the wall. Any search for username with '/' literal fails.
> I've searched through postgres mailing lists and all feed I could
> google out, and found some info about problem itself, but I was unable
> to find a solution for it anywhere.
>
> Problem was addressed in following locations:
> http://svr5.postgresql.org/pgsql-general/1999-07/msg00340.php
> http://svr5.postgresql.org/pgsql-sql/1999-03/msg00144.php
>
> But those posts are from 1999, and I did not find anything more up-to-
> date.
> Has anyone solved this problem?
> Is there any configuration option in PG allowing to make it behave in
> a more compliant way?
>
> I'd be more than happy to see something like Herouth Maoz suggested
> in
> http://svr5.postgresql.org/pgsql-general/1999-07/msg00340.php , namely
> configuration option for PG, but I was unable to find it.
>
> Sorry if I messed something up... I'm really very confused with this
> sftuff...
> I will appreciate any help or explanation, if I missed something
> important.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
Yes, I always tend to mix slash and backslash. No idea why ^^'.
Postgres version is:
PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mingw-special)
and it does have this setting, but it was set to off.
Now, having this set I'll have to test it :)
Thank you for your ansewrs and for directing me to proper group, I'll
ask such questions in -general in future.
Again, thank you a lot.
regards,
Thea
On 24 Wrz, 14:41, and...@dunslane.net (Andrew Dunstan) wrote:
> This is the wrong list to ask this question. This list is about
> development, not usage. Please ask on -general in future.
>
> I presume that where you have "/" you really mean "\".
>
> What version of postgres are you using? If you use a modern version with
> standard_conforming_strings on then you do not need to escape backslashes.
>
> cheers
>
> andrew
>
>
>
>
>
> Thea wrote:
> > Hello folks :)
>
> > I am working on a system that is possible to configure to work with
> > several DBMSes, including PG.
>
> > My problem is that PG behaves differently than other supported DBMSes
> > (MSSQL and MySQL) - when I'm passing a query containing LIKE phrase to
> > it, a double amount of '/' literals is needed to obtain expected
> > result. I do realize that this is caused by a parser 'collapsing'
> > double '/' to a single one.
> > I might not express this clearly, but - as I understand it - in
> > general result is that to find '/' literal in DB, '////' phrase is
> > needed ('////' is turned to '//' by a parser, and that is valid
> > expression for '/' literal in SQL itself).
>
> > The problem is that I cannot really use ESCAPE clause - it would
> > require working on too many files. (there are about 6k files in this
> > project, changing most of them would be not only not feasible, but
> > also risky)
> > The problem is that there is a table with user names. Those can
> > contain '/' literal (if they are domain users for example.) And here I
> > hit the wall. Any search for username with '/' literal fails.
> > I've searched through postgres mailing lists and all feed I could
> > google out, and found some info about problem itself, but I was unable
> > to find a solution for it anywhere.
>
> > Problem was addressed in following locations:
> >http://svr5.postgresql.org/pgsql-general/1999-07/msg00340.php
> >http://svr5.postgresql.org/pgsql-sql/1999-03/msg00144.php
>
> > But those posts are from 1999, and I did not find anything more up-to-
> > date.
> > Has anyone solved this problem?
> > Is there any configuration option in PG allowing to make it behave in
> > a more compliant way?
>
> > I'd be more than happy to see something like Herouth Maoz suggested
> > in
> >http://svr5.postgresql.org/pgsql-general/1999-07/msg00340.php, namely
> > configuration option for PG, but I was unable to find it.
>
> > Sorry if I messed something up... I'm really very confused with this
> > sftuff...
> > I will appreciate any help or explanation, if I missed something
> > important.
>
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majord...@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster- Ukryj cytowany tekst -
>
> - Poka cytowany tekst -