case sensitive sql search in vb.net

Question

So I've been using the query builder in visual studio (visual basic) to search a local mdb file. I have a button that is clicked to make a prompt for a search, and it works fine except that is is not case sensitive. Here is what I have so far:

SELECT ID, LastName, FirstName, FullTime, HireDate, Salary
FROM SalesStaff
WHERE LastName like ? + '%'

My professor wants us to use the InStr fuction, but how do I get that to work with a prompt?

(InputBox in my vb form code). Furthermore, it doesn't seem to have case sensitivity either. This is the first time I am using SQL, so I hardly know what I'm doing.

Thanks in advance!

Answer 1

You can amend the collation settings of your database/table. Alternatively if you just want a case sensitive comparison on this one statement you can use the collate keyword, such as below:

select 1 where 'abc' = 'ABC'
select 1 where 'abc' collate Latin1_General_CS_AS = 'ABC' collate Latin1_General_CS_AS
select 1 where 'abc' collate Latin1_General_CI_AS = 'ABC' collate Latin1_General_CI_AS
select 1 where upper('abc') collate Latin1_General_CS_AS = 'ABC' collate Latin1_General_CS_AS
select 1 where upper('abc') collate Latin1_General_CI_AS = 'ABC' collate Latin1_General_CI_AS

CI stands for case insensitive.

CS stands for case sensitive.