CONTAINS in oracle returns all rows when it encounters A and D characters

user_19834530 0
Viewed 44

I have a table E_TRAPLC_N with below values in ADDRESS column

NARROW WATER CASTLE E
NARROW WATER CASTLE B
NARROW WATER CASTLE C
NARROW WATER CASTLE A
NARROW WATER CASTLE A
NARROW WATER CASTLE D

I am using CONTAINS operator for searching words. When I use below queries

SELECT ADDRESS FROM E_TRAPLC_N A 
WHERE contains (ADDRESS, UPPER ('NARROW WATER CASTLE D')) > 0;
SELECT ADDRESS FROM E_TRAPLC_N A 
WHERE contains (ADDRESS, UPPER ('NARROW WATER CASTLE A')) > 0;

It returns all rows

NARROW WATER CASTLE E
NARROW WATER CASTLE B
NARROW WATER CASTLE C
NARROW WATER CASTLE A
NARROW WATER CASTLE A
NARROW WATER CASTLE D

But this

SELECT ADDRESS FROM E_TRAPLC_N A 
WHERE contains (ADDRESS, UPPER ('NARROW WATER CASTLE B')) > 0;

returns only

NARROW WATER CASTLE B

and

SELECT ADDRESS FROM E_TRAPLC_N A 
WHERE contains (ADDRESS, UPPER ('NARROW WATER CASTLE C')) > 0;

returns only

NARROW WATER CASTLE C

and so on

SELECT ADDRESS FROM E_TRAPLC_N A 
WHERE contains (ADDRESS, UPPER ('NARROW WATER CASTLE E')) > 0;

returns only

NARROW WATER CASTLE E

Why is that when I use contains (ADDRESS, UPPER ('NARROW WATER CASTLE A')) > 0 (or) contains (ADDRESS, UPPER ('NARROW WATER CASTLE D')) > 0 is returning all rows instead of respective rows?

full-text-search contains oracle
1 Answers

'A' and 'D' are part of the English language stoplist. So they are not considered as other chars in the index search. See here for the stop list See also this question for a discussion

user_73986530
Related