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!

The default MS SQL installation is not case sensitive. Here is how to temporarily or permanently change it. blog.sqlauthority.com/2007/04/30/… — Steve Wellens
– Steve Wellens, Commented Nov 24, 2014 at 23:43
ALTER TABLE is part of the core SQL standard....are you sure it didn't tell you that you don't have rights to the table? — Steve Wellens
– Steve Wellens, Commented Nov 25, 2014 at 1:52
I'd likely ask the professor why on earth he wants to use the InStr function? :) — Allan S. Hansen
– Allan S. Hansen, Commented Nov 25, 2014 at 7:48

JohnLBevan · Accepted Answer · 2014-11-25 00:23:22Z

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.

Collectives™ on Stack Overflow

case sensitive sql search in vb.net

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related