I have a requirement to be able to search several fields in a database using a case insensitive, accent insensitive search. Now bare with me, I'm a .Net developer who mostly uses T-SQL, and any changes that need to be done on the Oracle side need to go through one of our Oracle team...
The primary way I could find in Oracle to do an accent insensitive search is using OracleText with CONTAINS. This works beautifully, or did, until I starting trying to search the address field... For the record, the field in question is a VarChar2(240), nulls allowed.
So, some sample values...
184 2nd Concession
925, 33ième Rue
308 Ave. Louis-Hébert
1215 Boul. Beauséjour
169 Einstein E.
846 Louis Street
The Query
SELECT
ADDRESS
FROM
ADDRESS_TABLE
WHERE
CONTAINS(ADDRESS, '%Beausejour%') > 0 -- Works Great.
However
CONTAINS(ADDRESS, '%Louis-Hebert%') > 0 - Returns 1 Row - 846 Louis Street...
The dash acts as a 'minus', so it looks for any Louis without Hebert...
To Fix that, I can incase the string with {},
CONTAINS(ADDRESS, '{%Louis-Hebert%}') > 0 - Returns 1 Row - 308 Ave. Louis-Hébert
but that eliminates any partial words
CONTAINS(ADDRESS, '{%Louis-Hebe%}') > 0 - Returns 0 Rows.
Now, since I'm using C# as a front-end, I can replace the dash before passing a parameter, but I wanted to know if there was any way to include the dash, and still include part matches..