4

I have an Access database set up on a domain hosting service. I am connecting to it through SQL. However, I need all of my queries to be case sensitive, and as far as I know the way the server is set up on the hosting service is it's NOT case sensitive. Is there a certain command that I could use in my SQL which would make the query case sensitive?

Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
futurevilla216
  • 982
  • 3
  • 13
  • 29

2 Answers2

5

Do you need to set the entire DB to case sensitive, or is it just part of some queries. If it is a query term then you can use these to force case sensitive matching:

StrComp("A","a",0)

The 0 in the method signature is to perform a binary comparison giving you the case sensitivity you want. It returns an integer. 

WHERE StrComp('myText1', 'MYTeXt1', 0) = 0

Documentation

Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
  • I need my entire DB to be case sensitive. – futurevilla216 Apr 21 '11 at 17:46
  • When I tried `WHERE ASC(text1) = ASC(text2)` I get an invalid procedure call. For the first one you suggested, how would that work? Would I just type `WHERE StrComp('myText1','MYTeXt1')`? – futurevilla216 Apr 24 '11 at 16:34
  • I just tried it, and I am getting the same thing as the ASC. How would you use StrComp if I am comparing it to a whole field? Technically, it's not a string. – futurevilla216 Apr 25 '11 at 06:22
3

I think you can add collate after the WHERE clause. 

SELECT col FROM table  
WHERE col COLLATE Latin1_General_CS_AS = 'value'
Niklas
  • 13,005
  • 23
  • 79
  • 119
  • Thanks, will try that now. If I have WHERE col COLLATE abc = 'value' AND... I would put the collate after the AND as well, right? – futurevilla216 Apr 21 '11 at 17:45
  • When I am using UPDATE, I would not need the COLLATE, correct? – futurevilla216 Apr 21 '11 at 17:49
  • That depends. If you want to update where *'value' = 'value'* but not where *'value' = 'VALUE'* then you do need to use collate. – Niklas Apr 21 '11 at 17:54
  • I just tried it, and I am getting a syntax error. I am missing an operator somewhere. Here is my SQL statement: `SELECT * FROM table WHERE username='username' AND password COLLATE password='password'` Where did I go wrong? – futurevilla216 Apr 21 '11 at 23:29
  • Never mind, I made a mistake :) Now, I have `SELECT * FROM table WHERE username COLLATE Latin1_General_CS_AS = 'username' AND password COLLATE Latin1_General_CS_AS = 'password'`. I am still getting a syntax error (missing operator). – futurevilla216 Apr 22 '11 at 00:55
  • @Niklas I already accepted it before, I must have accidentally unaccepted it. Sorry. – futurevilla216 Apr 22 '11 at 15:37
  • What happens if you place collate at the end instead? `SELECT * FROM table WHERE username = 'username' COLLATE Latin1_General_CS_AS` – Niklas Apr 22 '11 at 15:49
  • Same problem. Is there any way to change the collation for the whole database for all new records in the DB? I don't necessarily need to put the COLLATE for every query if the whole database is already set on a certain collation, right? – futurevilla216 Apr 23 '11 at 15:11
  • Oh, I thought you didn't have access to the DB? – Niklas Apr 23 '11 at 18:21
  • `ALTER TABLE table ALTER COLUMN username varchar(10) COLLATE Latin1_General_CI_AS NOT NULL` – Niklas Apr 23 '11 at 18:23
  • @Niklas I have access to the DB, but I don't have access to the server it is running on – futurevilla216 Apr 24 '11 at 15:52
  • @Niklas when I try your idea, I get a syntax error on the `ALTER TABLE` statement. Is there anything I need to do to the DB before running this code? Right now, that is the first query that is run. Also, remember I am using the Microsoft ODBC driver, if that makes any difference. – futurevilla216 Apr 24 '11 at 16:02
  • It is apparent from the chain of comments that this answer never worked, despite @futurevilla216 marking it as the accepted answer. To be clear for future readers, **This answer is not correct for MS Access (Jet/ACE) databases. COLLATE is not a valid SQL keyword for MS Access queries.** – C Perkins Jul 13 '17 at 16:19