On Tue, May 19, 2015 at 7:22 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Mike Blackwell <mike.blackw...@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.
