Case insensitivity, and option?

Case insensitivity, and option?

Post by o » Mon, 24 Mar 2003 22:21:20

Quote:> select * from table where field ILIKE 'blAH';  -- ;-)
> is almost as easy :-)
> PS: no, don't do this if you want portability. I think the charset
> idea's a better one.

> Ron

select * from table where lower(field)=lower('BLah') will break
portability too in the sense that many DBs (perhaps all commercial
ones) do not support functional indexes. Hence
lower(field)=lower('BLah') query will not be using index when it runs
on those DBs.

Besides, the developer must always remember to use conversion when
writing queries. This does not sound bad initially but in a big
application with many developers and complex query logic this will
create quite a few annoying, time consuming and difficult to find bugs.

Case insensitive charset definitely sounds like a better idea. Has
anyone done this yet? IMHO, this should be a part of core distribution
since 99.999999% of queries do not need case sensitivity.


Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?


1. Case insensitivity, and option?

I was at a client office reviewing some code. They use MSSQL and I
noticed that:

select * from table where field = 'blah';
gave the same results as:
select * from table where field = 'BLah';

I was shocked. (a) because I know a lot of my code could be easier to
write, and (b) that their code would break on every other database I am
aware of. Does anyone know about this?

Is it practical/desirable for PostgreSQL to have this as a configuration

---------------------------(end of broadcast)---------------------------

2. call for participation

3. Case-insensitivity detected by ODBC Driver

4. trouble with file Assertion

5. Simple Case Insensitivity

6. FL Great Opportunities in most advanced technologies

7. Case Insensitivity

8. Outlook contacts as datasource !?

9. Case insensitivity and Trims

10. Help! Case Insensitivity with SELECT..WHERE..LIKE queries

11. SYBASE - case-insensitivity of its data

12. Help! Case Insensitivity with SELECT..WHERE..LIKE queries

13. Case Insensitivity