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.

---------------------------(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

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 -