Query Match is Case Sensitive

Question

I have the following query, looking for salespeople of 'BG'.

SELECT *
  FROM [dbo].[JobOrders]
  where [salesperson] = 'BG'

When I use 'bg' instead, I do not get results. To my understanding 'BG' or 'bg' would bring back the same results.

Is there a setting that would prevent this?

Depends on your collation pf your Sql Server install. See this [question and answer][1]. [1]: http://stackoverflow.com/questions/14962419/is-the-like-operator-case-sensitive-with-ms-sql-server — mxmissile, Mar 20 '14 at 14:21

score 0 · Answer 1 · answered Mar 20 '14 at 14:15

0

To avoid this situation you can always check using Upper cases

SELECT *
  FROM [dbo].[JobOrders]
  where upper([salesperson]) = 'BG'

answered Mar 20 '14 at 14:15

KrazzyNefarious

3,202
3
20
32

Valid answer but be aware that the benefit of any indexes will likely be lost. – Steve Martin Mar 20 '14 at 14:22

score 0 · Answer 2 · answered Mar 20 '14 at 14:45

Your salesperson column is almost certainly a foreign key and so would likely be populated by consistently cased values (ie they should be all upper case), so:

SELECT *
FROM [dbo].[JobOrders]
WHERE [salesperson] = UPPER('bg')

This would allow an index to still be used for the salesperson column.

Jayvee · Answer 3 · 2014-03-20T16:48:50.973

0

You can make it case insensitive by using collation:

SELECT *
FROM [dbo].[JobOrders]
where [salesperson] = 'BG' COLLATE SQL_Latin1_General_CP1_CI_AS

edited Mar 20 '14 at 16:48

answered Mar 20 '14 at 16:20

Jayvee

10,670
3
29
40

OP wanted case insensitive results. – Kapol Mar 20 '14 at 16:45

Query Match is Case Sensitive

3 Answers3