Case Sensitive search 2

[crashevans]

Hi

I want to search a table for a case sensitive result e.g. "Blue", however it currently returns this result whether I search for BLUE, blue etc. I am using Microsoft Access.

The query I tried -
SELECT * FROM User WHERE Password = 'Blue'

Thanks for help in advance.

 

[gmmastros]

The syntax for case sensitive searching in Access is probably different than SQL Server, which this forum is for. I suggest you post your question in one of the Access Forums.

Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hmckillop]

As far as I know Case sensitivity in SQL is an install option (as default not case sensitive). As this is Microsoft Access, I dont even know if its got this option but best try the Microsoft Access forum.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[gmmastros]

In SQL Server, case sensitivity is based on the collation for the column. In this example, I create a table variable with a Data column. Since I don't specify the collation for the column, it defaults to case insensitive (on my server).

The first 2 selects return the same data because the first one uses the default collation and the 2nd one uses a collation that is the same as my default. The first 2 selects return both records.

The third query specifies a case sensitive collation, so only 1 record is returned.

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values('Blue')
Insert Into @Temp Values('BLUE')

Select * From @Temp Where Data = 'Blue'
Select * From @Temp Where Data Collate SQL_Latin1_General_CP1_[!]CI[/!]_AS = 'Blue'

Select * From @Temp Where Data Collate SQL_Latin1_General_CP1_[!]CS[/!]_AS = 'Blue'

The parts in red determine the case sensitivity.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hmckillop]

Good to know George, again showing up my ignorance.
I must have 100 sample scripts now stored off - probably 95% of them yours!

"I'm living so far beyond my income that we may almost be said to be living apart

 

[ilyad]

You may also want to check this link

http://www.umachandar.com/technical/SQL2000Scripts/Main13.htm

 

[gmmastros]

ilyad,

Thanks for sharing that link. It was very interesting.

By searching for the non-case sensitive value AND the case sensitive value (2 conditions in the where clause), it will cause an index seek instead of an index scan which would be better for performance.

I'll have to remember this one!

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Also take a look at thread183-1361786

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ilyad]

Yes, an interesting approach. Found this link doing google search and decided to share.

 

[Cornelius19]

Hi,

How can George’s code be combined with wildcards? I tried the following but it did not return any result:

Select * From @Temp Where Data Collate SQL_Latin1_General_CP1_CS_AS = 'Blu%'

Any idea?

Cornelius

 

[SQLDenis]

Change = to LIKE

then read this
Make Your Case Sensitive Searches 1000 Times Faster

http://sqlservercode.blogspot.com/2007/05/make-your-case-sensitive-searches-1000.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLDenis]

Select * From @Temp Where Data LIKE 'Blu%'Collate SQL_Latin1_General_CP1_CS_AS

now in order to use an index just do this

Select * From @Temp Where Data LIKE 'Blu%' Collate SQL_Latin1_General_CP1_CS_AS
and Data LIKE 'Blu%' --this will use an index


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ilyad]

I think you should use LIKE instead of =

 

[Cornelius19]

Thanks, I replaced = with LIKE and it works fine!

Cornelius

 

[Glasgow]

I didn't know how to force a case sensitive comparison until I read this thread. Star to gmmastros, thanks.