Re: Problems with question marks in operators (JDBC, ECPG, ...)

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

> Mike Blackwell <mike(dot)blackwell(at)rrd(dot)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.