Results 1 to 1 of 1
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: I wanted to use COLLATE within CONTAINS

    SQLSERVER2000
    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

    ...Where Contains(DocumentDetail.SGML, @SearchText)

    Any ideas on what I can do to simulate the use of the Contains function and apply Collation to my search term??
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •