Unanswered: I wanted to use COLLATE within CONTAINS
I am trying to jerry-rig in case sensitivty into an existing search architecture
The search strings contain operators (and,or) or a quoted phrase
I have a select statement and I am trying to find my search string in a varchar field containing text (it happens to be SGML code but that is not at issue here)
Where Contains(DocumentDetail.SGML,@SearchText COLLATE SQL_Latin1_General_CP1_CS_AS)
the Where statement I am trying to add this to is show in the attached file below the commented line CANT ADEQUATELY ADD CASE SENSITIVITY
trying to create this I get a syntax erorr "Error 156: Cannot use empty object or column names. use a single space if necessary. Incorrect syntax near the keyword 'Collate'."
the code blow will works with single word comparisons
Where DocumentDetail.SGML like '%' + @SearchText COLLATE SQL_Latin1_General_CP1_CS_AS + '%'
Any ideas on what I can do to simulate the use of the Contains function and apply Collation to my search term??
I have attempted to Declare the @SearchText variable with the COLLATE but don't beleive that is possible
When I use the following
Set @SearchText = (Select Text COLLATE SQL_Latin1_General_CP1_CS_AS From....
The syntax is correct but the search term behaves as if it does not have the collation applied when I use it here