Re: [sqlite] Where statements are they case sensitive?
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?
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?
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]
[sqlite] Where statements are they case sensitive?
Hi All, 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?? regards Greg O