Re: [sqlite] Where statements are they case sensitive?

[Greg Obleshchuk]

  I checked a few systems and there doesn't seem to be a standard.  As was said Oracle 
is case sensitive but all the Microsoft products I check SQL, Access , MS Query where 
all case insensitive.

  I think a PRAGMA would be a great idea for this.  What do other people think?
  Using a like (which negates the use of indexes or a lower function which does the 
same si a solution but not the best )

  regards
  Greg 

Re: [sqlite] Where statements are they case sensitive?

[Danny Reinhold]

Hi!

> I just checked something and noticed that the WHERE statement is case
sensitive.  I have check this in SQL Server and it is not case sensitive.
>
> I am using 2.8.5 and 2.8.6.
>
> As an example in the northwind DB I have for SQLite .  There is a table
called Orders
> select * from sqlite_master where Name = 'orders'  return no rows but
> select * from sqlite_master where Name = 'Orders'  does return rows
>
> but
>
> create table orders(a) returns an error with the table already exists.
I don't know MS SQL Server and I don't know what you did there.
But I think:
Strings that are enclosed in ' are always case sensitive.
Column and table names are always case insensitive.

So it does not matter if you write:
select a, b, c from mytable;
select A, B, C from MYTABLE;
or
select a, B, c from myTable;

But it does always matter if you write:
select * from mytable where a = 'hello';
select * from mytable where a = 'Hello';
or
select * from mytable where a = 'hElLo';

If you search a table name in sqlite_master, then you do a string
comparision and that is case sensitive.
If you create a table you don't use a string literal but a table name
and that is case insensitive.

This behaviour looks very straight and correct to me and I think
it is standard SQL behaviour.

What exatly did you do to get another result with MS SQL Server?


> Should the where statement be case sensitive , By default I don't think it
should.
>
> Should I report a bug on this or was it by design??
I think it is very well designed and should not be changed.

If you want case insensitive where clauses, use something like this:
select * from sqlite_master where upper(name) = 'MYTABLE';

  - Danny

--
Danny Reinhold
Reinhold Software & Services



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Where statements are they case sensitive?

[Paul Smith]

I just checked something and noticed that the WHERE statement is case 
sensitive.  I have check this in SQL Server and it is not case sensitive.

I am using 2.8.5 and 2.8.6.

As an example in the northwind DB I have for SQLite .  There is a table 
called Orders
select * from sqlite_master where Name = 'orders'  return no rows but
select * from sqlite_master where Name = 'Orders'  does return rows

but

create table orders(a) returns an error with the table already exists.

Should the where statement be case sensitive , By default I don't think it 
should.

Should I report a bug on this or was it by design??
I think MS SQL Server is the odd one out here. Oracle is case sensitive by 
default, as are many others.

Personally, I'd like a pragma or something to make index searches case 
insensitive, but I get by by forcing all case insensitive search fields to 
upper case when storing to the database and when doing the search.

(You can do case insensitive searches by using "field like 'xyz'" instead 
of "field='xyz'" or "upper(field)='XYZ'", but these won't use the indices, 
so it'd be nice to be able to set the case sensitivity of indices individually)



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]