--creating preferences etc 
exec   ctxsys.ctx_ddl.create_preference ('cust_lexer', 'BASIC_LEXER'); 
exec   ctxsys.ctx_ddl.set_attribute ('cust_lexer', 'base_letter', 'YES'); -- removes diacritics 
exec   ctxsys.ctx_ddl.create_preference ('cust_wl', 'BASIC_WORDLIST') 
exec   ctxsys.ctx_ddl.set_attribute ;    ('cust_wl', 'SUBSTRING_INDEX', 'true'); 
 
--table set up 
CREATE TABLE TEST_USER 
( 
  FULL_NAME  VARCHAR2(64 CHAR)                  NOT NULL, 
  LAST_NAME  VARCHAR2(64 CHAR)                  NOT NULL 
); 
 
 
CREATE INDEX TEST_USER_IDX5 ON TEST_USER 
(FULL_NAME) 
INDEXTYPE IS CTXSYS.CONTEXT 
PARAMETERS('LEXER cust_lexer WORDLIST cust_wl SYNC (ON COMMIT)'); 
 
--data set up 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('Supervisor upervisor', 'upervisor'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('JOSEPH WILSON', 'WILSON'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('CHRISTOPHER Phil', 'TAYLOR'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('PAUL HERNANDEZ', 'HERNANDEZ'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('Betty Jipes', 'Jones'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('One Anna', 'Anna'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('Three Anna', 'Anna'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('Two Anna', 'Anna'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('Four Anna', 'Anna'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('Gary Barrow', 'Barrow'); 
COMMIT; 
 
--query using CONTAINS which gives WRONG output - because out intention is to return only those strings that START WITH the letter P but it returns another string that has P not at the starting!! 
 
SQL> select full_name from test_user where contains(full_name,'P%')>0; 
 
FULL_NAME 
---------------------------------------------------------------- 
PAUL HERNANDEZ 
CHRISTOPHER Phil 
 
-- I need output as follows in which the second value is not returned...meaning 'CHRISTOPHER Phil' should NOT be returned by the CONTAINS clause just as the LIKE operator below doesn't return it. 
SQL> select full_name from test_user where full_name like 'P%'; 
 
FULL_NAME 
---------------------------------------------------------------- 
PAUL HERNANDEZ