Case insensitivity and Trims

Case insensitivity and Trims

Post by Ee » Sat, 23 Mar 2002 13:44:12



Hi,
   I'm still trying to figure out an easy way to deal
with case insensitive queries systematically through
a ddl command.  I tried something like:

CREATE FUNCTION UTrim (varchar) RETURNS varchar AS 'select
upper(trim(both from $1)) as result' LANGUAGE 'sql' with (iscachable)

create index poindex on shop (UTrim(purchase_order))

so I would assume all queries by purchase_order on shop would be
case insensitive and insensitive of leading + trailing spaces
save in the purchase_order column.

This isn't the case.  Am I missing something?

Anyway, I'm worried that something like:

select * from shop where UTrim('po1') = UTrim(purchase_order) would be
inefficient.  Does creating the index UTrim(purchase_order) speed that
query up?  Is UTrim required in both the create index and select
statements to work properly and speedily?

Thanks!

 
 
 

1. Case insensitivity in LIKE query - possible?

Hello,

I'm using Oracle 7.1 on an SGI, and would like to perform some case
insensitive queries using LIKE.  I've looked through several SQL books,
and have found nothing addressing the subject.  Can this be done?

Currently the query:

SELECT * from foo where name LIKE 'James';

will match:

James
James Cooper

but not:

JAMES

...

Any suggestions appreciated.

-- James

.........................................................................
                        James Paul Cooper * Pixel
                         MU College of Education

.........................................................................

2. Alternate execution plans?

3. SYBASE - case-insensitivity of its data

4. SQL server 7 and full-text search

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

6. Pay rates!!!

7. Case Insensitivity

8. Replication agents have disappeared

9. can there be case insensitivity with sql???

10. Case sensitivity/insensitivity select...like '%...%'

11. Case insensitivity, and option?

12. Simple Case Insensitivity

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