Re: Problems with question marks in operators (JDBC, ECPG, ...) - Mailing list pgsql-hackers

On Tue, May 19, 2015 at 7:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Mike Blackwell <mike.blackwell@rrd.com> writes:
> See for example
> http://docs.oracle.com/cd/B19306_01/text.102/b14218/cqoper.htm#i997330,
> Table 3-1, third row, showing the precedence of '?'.  Further down the
> page, under "Fuzzy" see "Backward Compatibility Syntax".

If I'm reading that right, that isn't a SQL-level operator but an operator
in their text search query language, which would only appear in SQL
queries within string literals (compare tsquery's query operators in PG).
So it wouldn't be a hazard for ?-substitution, as long as the substituter
was bright enough to not change string literals.

                        regards, tom lane

That's how I read it too. I've tried this little test: http://sqlfiddle.com/#!4/7436b/4/0

CREATE TABLE test_table (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100)
);

INSERT INTO test_table (id, name) VALUES (1, 'Nicole');
INSERT INTO test_table (id, name) VALUES (2, 'Nicholas');
INSERT INTO test_table (id, name) VALUES (3, 'Robert');
INSERT INTO test_table (id, name) VALUES (4, 'Michael');
INSERT INTO test_table (id, name) VALUES (5, 'Nicola');

CREATE INDEX idx_test_table_name ON test_table(name) INDEXTYPE IS CTXSYS.CONTEXT;

SELECT * FROM test_table WHERE CONTAINS(name, '?Nicolas', 1) > 0;

Fuzzy matching works indeed, but the question mark is part of the literal (similarly to % when using LIKE).

Best wishes,

Bruno.