×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Case sensitive request

Case sensitive request

Case sensitive request

(OP)
If I have data like this:

FirstName
Andy
angela
Bob
bill

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:

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


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

---- Andy

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

RE: Case sensitive request

I'd check this:

CODE

SELECT SERVERPROPERTY('COLLATION') 

CODE

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

CODE

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:

CODE -->

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

Chriss

RE: Case sensitive request

(OP)
It works! Great, thank you. thumbsup2

Then I've got carried away and tried:
...
Where Substring(FirstName, 1, 1) IN ('A') COLLATE sql_latin1_general_cp1_cs_as

and that does not work. But well, you cannot get everything, right? smile

---- Andy

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

RE: Case sensitive request

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

CODE

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

Chriss

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close