CODEWITHSUNDEEP
contains
Omer Ciftci
Omer Ciftci

Reputation: 987

Case sensitive search using CONTAINS keyword in SQL Server

I want to search word from my database, it must be case sensitive. I added full text index feature to my SQL Server and implemented to my table. And I want to use CONTAINS keyword for searching which is case sensitive. Is it possible? 

SELECT * 
FROM ResultDatabase
WHERE CONTAINS(Data ,'letS')

Thank you

Upvotes: 1

Views: 3685

Answers (1)

derpirscher
derpirscher

Reputation: 17407

Whether string comparisons are case sensitive or not (and other behaviours regarding character operations) is defined by the collation of a database. See docs for details.

You will either have to create a new database with a case sensitive collation

CREATE DATABASE MyOptionsTest  
COLLATE French_CS_AS;  -- CS denotes a CaseSensitive Collation CI would denote a CaseInsensitive Collation
GO

or update your existing database to a case sensitive collation

USE master;  
GO  
ALTER DATABASE MyOptionsTest  
COLLATE French_CS_AS;  
GO

Source: docs

EDIT If you need, both, case sensitive and insensitive search on the same database you could either

  • use a case sensitive collation and for case insensitive searches use UPPER. This may fail with some special characters, for instance if they don't have an uppercase variant.

SELECT * FROM someTable where UPPER(somecolumn) = UPPER('searchterm')

  • explicitely specify the collation in the query

SELECT * FROM someTable where somecolumn COLLATE French_CS_AS = 'SEARCHTERM'

Upvotes: 1

Related Questions