CONTAINS with middle wildcard
Hi,
I have a table with two columns: user_id and username, containing 5 rows:
user_id user_name
1 Bob Smith
2 David John Strum
3 John Stein
4 John Stuart
5 John Snow
Then I use this select to get all users that match a certain filter: select * from users where username like ('%John%St%');
This will return row 2, row 3 and row 4.
How can I use the CONTAINS function of Oracle Text in order to achieve the same result?
I've tried by adding preferences for basic wordlist, prefix, wildcard maxterms etc but it doesn't work. If I use contains('John%') it works but if I add something else after the wildcard I receive no results.