Force Case Sensitivity with Contains operator

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Force Case Sensitivity with Contains operator

Author

Topic

MonaKamel
Starting Member

1 Post

Posted - 2008-03-04 : 07:28:27

Hi,

i am writing a stored procedure to perform a search on unicode text. the stored procedure takes as parameters: @SearchText (text to search for) , @Filter ("Like" , "Contains", ...), and @CaseSensitive as boolean to indicate whether or not the search is case sensitive.

In case when the user chooses a CaseSensitive search, i modify the query and force a collation with Case Sensitivity (CS), for example, SQL_Latin1_General_CP1_CS_AI.

The problem lies in the fact that specifying a collation works with "Like" and "=" operators of a where clause, but it does not work with the "Contains" or "Freetext" operators (knowing that Contains and Freetext are case-insensitive, i want to FORCE case sensitivity only in the sql query, i don't want to change the case sensitivity on the database or the field i'm searching on because i only want it to be case sensitive in specific conditions where a user wants his search to be case sensitive)

I posted herebelow the sql query with the Like operator (@Filter ="Like") and a Case Sensitive collation (when @CaseSensitive is True)

SELECT .QueryText,

FROM QueryTerms

WHERE (QueryText like '' @SearchText '' COLLATE SQL_Latin1_General_CP1_CS_AI)

Does anyone have an idea how to FORCE case sensitivity when using "Contains" or "FreeText" operator in the below query,if doable!
Thanks

SELECT QueryText

FROM QueryTerms

WHERE (contains(QueryText, '"' + @SearchText + '*"'))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources