Case sensitive request 1

[Andrzejek]

If I have data like this:
[tt]
FirstName
Andy
angela
Bob
bill
[/tt]
And I want a list of names that start with a Capitol 'A' or 'B', or lower case 'a' or 'b', so I've tried:
[tt]
Select FistName From MyTable
Where Substring(FistName, 1, 1) = 'A'[/tt]

and that does not work, I get records: Andy and angela :-(
How can I request data 'case sensitive'

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

I'd check this:

SELECT SERVERPROPERTY('COLLATION')

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

select table_name, column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @table_name

In the collation names cs stands for case sensitive and ci for case insensitive, as and ai are for accent sensitive vs insensitive as a separate specification of how comparisons are made.

To get a case sensitive result the ideal situation is defining the collation as needed on the level of server, database, table or column when they are defined. If you have no influence you can make a comparison case sensitive by explicitly forcing the collation you need, for example to get firstnames with capital A only:

Select FistName From MyTable Where Substring(Firstname,1,1) = 'A' COLLATE sql_latin1_general_cp1_cs_as

Chriss

 

[Andrzejek]

It works! Great, thank you.

Then I've got carried away and tried:
[tt]...
Where Substring(FirstName, 1, 1) IN ('A') COLLATE sql_latin1_general_cp1_cs_as [/tt]
and that does not work. But well, you cannot get everything, right?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

I think then it would work like this. Not sure.

...Where Substring(FirstName, 1, 1) COLLATE sql_latin1_general_cp1_cs_as  IN ('A')

Chriss