CODEWITHSUNDEEP
sqlsql-server
golldy
golldy

Reputation: 1299

sql server querying values in upper case

I am trying to query a sql server table which has values in upper case and it doesn't return anything. For example,

Table structure,

ID  Fruit
--  ------
1   APPLE ONE
2   ORANGE TWO
3   PEAR THREE

Select * from Fruits where fruit = 'APPLE ONE'

Does not return anything. But if i change it to "apple one" in the database and change the query to 

Select * from Fruits where fruit = 'apple one'

it works.

how do u get this work with upper case data ?

Upvotes: 0

Views: 1257

Answers (3)

user2970362
user2970362

Reputation: 384

Make sure your column is set to the correct collation. Or you can specify the collation in your query directly.

Collations with CI are Case Insensitive. This will return your 'apple one' record:

select 'CI', * from table1
where myfield = 'apple one' collate SQL_Latin1_General_CP1_CI_AS

This will not return your 'apple one' record because it is CS (case sensitive):

select 'CS', * from table1
where myfield = 'apple one' collate SQL_Latin1_General_CP1_CS_AS

If all your queries use the same collation for this column, it's best to set it on the column. If all your queries use the same collation on all columns, it's best to set it on the database itself as a default.

Eg. Setting it for the column:

CREATE TABLE Table1
    ([myfield] varchar(10) collate SQL_Latin1_General_CP1_CI_AS)
;

Upvotes: 4

Santhosh
Santhosh

Reputation: 1791

In these cases, the best things is to convert both sides of EQUAL oeprator to either UPPER or LOWER

Select * from Fruits where UPPER(fruit) = UPPER('apple one')

or

Select * from Fruits where LOWER(fruit) = LOWER(apple one')

Upvotes: -1

Hogan
Hogan

Reputation: 70538

You can use the UPPER or LOWER function. Like this:

Select * from Fruits where UPPER(fruit) = 'APPLE ONE'

Also like op. will ignore case:

Select * from Fruits where fruit like 'APPLE ONE'

Upvotes: 1

Related Questions