CODEWITHSUNDEEP
sqlvb.netcase-sensitive
Genoe
Genoe

Reputation: 13

case sensitive sql search in vb.net

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!

Upvotes: 0

Views: 1028

Answers (1)

JohnLBevan
JohnLBevan

Reputation: 24410

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.

Upvotes: 1

Related Questions