Oracle and Case

[nitai_co]

Hi all,

When working with a Oracle DB how can we search for phrases or strings case
insensitive? Should we just store all text in upper or lower cases in the DB?
If so then a problem arises that text is not entered exactly as the user wanted.

As an example: One would use a Content Management System and User would enter
his text for the website. Then pulling that text would show all in upper or
lower case when retrieving from the db, no? What about searching? I mean we
cant force our customers to only search in lower case.

How does anybody overcome this?

TIA for any advices.

Nitai

[mxstu]

I don't use oracle, but I've seen other posts where the developer just converted all terms to the same case.

WHERE LOWER(someColumn) = '#LCase(form.someField)#'

[nitai_co]

I see.

So actually one stores the entries of the Users "as is" and then converts
search inputs to upp/lower cases and then also converts the data in the db to
upper/lower case to compare on the fly.

Looks to me like a little bit of a overkill. Anyhow, what would be better to
convert to upper or lower case? Guess that doesn't matter?

[mxstu]

If you import the data "as is", then It's not overkill, it won't work otherwise.

I think upper vs lower matters.

[nitai_co]

Sorry, I meant to write that I think it is a overkill to convert the data
before searching within the where of the query.

I read on the Oracle Technote that one should not use upper() or lower() a lot
because of the performance hit on the database. But I guess one can overcome
the case problem with a index which then again is caseinsensitive. Since I am
searching only a field with short values, for me a index would be too much.

About upper/lower case. I have read that a lot of developers prefer uppercase,
but I have yet to see any reasons for it.

[mxstu]

>I read on the Oracle Technote that one should not use upper() or lower() a lot
because of the performance hit on the >database. But I guess one can overcome
the case problem with a index which then again is caseinsensitive. Since I am

>searching only a field with short values, for me a index would be too much.

Yes, there is definitely a performance hit when using functions. As far as
the indexes I guess it depends on the database. It used to be that using
functions could sometimes cause the database to ignore an indexes completely.
However, I think the behavior depends on the database type. I'm not sure how
oracle handles it.

>About upper/lower case. I have read that a lot of developers prefer

uppercase, but I have yet to see any reasons for it.
Yes, I seem to remember that from years ago. I don't recall the reason either.

What version of oracle are you using? I take it that it is not one of the
newer ones that has case insensitive settings?

[mxstu]

>I think upper vs lower matters.
That should be I do *not* think it matters. My typing is awful today ;-)

[nitai_co]

We are using 10g. What caseinsensitive switch? I found nothing in the documentations?

[jonwrob]

Have you thought about Oracle Text? If you're doing a CMS, that might be appropriate.

JR

[mxstu]

I don't think it has a global switch per se, like ms sql. I was thinking of [url]http://www.oracle.com/technology/oramag/oracle/03-sep/o53sql.html[/url].

[nitai_co]

Have you thought about Oracle Text?

We are rather new to Oracle. I have read about Oracle Text but actually dont know how to use it. Do you mabye have a snippet?

TIA

[jonwrob]

It's all in the manuals. Specifically, look at the Oracle Text Application Developers Guide. Read it, experiment and have fun.

JR