I have been fighting this issue for a while
I would like to use a FULL Text Seach query to find Files on a windows file system.
The problem is when the case comes up where there is a parentheses in the file name. This example was created from a copy being pasted in the same location as the original a few times.
Here is my code:
DECLARE @SearchWord VARCHAR(50)
SET @SearchWord = '"KAP1556PP_P01(2).jpg"'
SELECT
[FileName],
[FilePath]
FROM
[dbo].[tblLegalPlan]
WHERE
CONTAINS(*, @SearchWord)
When I use Double quotes for the @Searchword I recieve no results.
When I use no quotes for the @Searchword I recieve an error message:
Msg 7630, Level 15, State 2, Line 8
Syntax error near '(' in the full-text search condition 'KAP1556PP_P01(2).jpg'.
When I search for files containing TEXT up to the bracket i Get undesrired results obviously:
SET @SearchWord = '"KAP1556PP_P01*"'
Results
FileName FilePath
KAP1556PP_P01(1).jpg BC\EPP\KAP
KAP1556PP_P01(2).jpg BC\EPP\KAP
Any and all iterations of the above show to not be usefull. There are many articles of people trying to parse the search terms and work it out, but I have not come accross any that can handle this situation. A solution would be appreciated. Thanks
The work around to this for me was to execute a Stored Procedure that would remove and/or replace the brackets in question, allowing the FULL TEXT indexing to work.