Any tricks for case insensitive searches?

[jballum]

Does anyone know if their are any tricks around searching a character field without having to worry about case sensitivity?

Other then using UPPER(fieldname) = UPPER(searchVariable)

Thanx

JB

 

[MikeLacey]

I've quite often got around this problem by creating another column in the table which is filled automatically with the UPPER (or LOWER, for that matter) version of your existing column

You can then say: Where auto_field = UPPER(searchvariable)

This also comes in handy for fields where users are likely to try and format the number but not consistently (telephone numbers for instance) - for telephone numbers I strip out everything but the numbers in the trigger and store that in a column the user never sees. This column is then nice and easy to search on. Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[Guest_imported]

If you don't want to have to maintain another column but still want to be able to use the index (upper kills the index), you can do it like Oracle Forms does it internally when you use case insensitive query.

What you have to do is compare all 4 possible combinations of the two first letters of your search to the database column, before you do your upper part. That way the number of rows will be minimized considerably before the upper part comes along. If the varians of your data is OK, which means that not all of your records starts with the same two letters, the upper part will only have to do a scan on maybe 1 percent of your records.

Here's an example:

Let's say "KKM" is the table you want to do your search in, "a" is the column you want to search and "con" is the constant you want to search for:

select a
from kkm
where (a like upper(substr(con,1,2))||'%'
or a like upper(substr(con,1,1))||lower(substr(con,2,1))||'%'
or a like lower(substr(con,1,1))||upper(substr(con,2,1))||'%'
or a like lower(substr(con,1,2))||'%')
and upper(a)=upper(con);

I've done case insencitive searches in tables with millions of records and gotten my answer back within miliseconds, using this technic.

Regards
Klaus Mogensen

 

[MikeJones]

Although I like the two other solutions (I must admit I've not tried the later one but will when I get a chance) Oracle have supplied some base functionality to deal with this type of requirement.

Using Oracle's new Function based indexes you could build an index based on the column converted to either Upper or Lower case. This would allow index speed access when using the correct Function (upper or lower depending on which one you used in query). Look up function based queries in your documentation, if memory serves me correctly (which I must admit it rarely does) the example is even on using Upper as the basis for the function based index.

I've not used it in a production environment but have played around with it and founs the results to be more than acceptable. As far as I'm aware you can also then analyse the index to calculate such things as data skew and allow the CBO to decide if it should use the index at all.

HTH,

Mike.

 

[jballum]

Thanx for all your suggestions. I have talked the customer into letting me convert all existing data to upper case. And any other data will be converted before inserting.

JB