Re: String Search in Oracle Database

Two possibilities spring to mind;

1. Use Select bla bla bla FROM my_table WHERE UPPER(my_table.my_column) = UPPER(my_user_selection)

However this could be very slow as oracle cannot use an index when a function is applied to a column (UPPER(my_table.my_column))

2. Create another column on the table called, say, my_column_upper and use a trigger to set its value to UPPER(my_column).

       Create an index on my_column_upper
       The SQL statement now becomes
            Select bla bla bla FROM my_table WHERE my_table.my_column_upper
= upper(my_user_selection)
        you can use the original column (my_column) for display purposes

This is a bit more time consuming to implement and uses more storage but it should be quite quick.

Stewart Burnett

Please remove 'nospam' from address when replying direct.

Lim Wee Shoong <lweeshoo_at_starnet.gov.sg> wrote in message news:36B82E7A.B0640BC_at_starnet.gov.sg...
>Hi,
>
>I understand that Oracle stores string (varchar2) exactly as the way the
>user enters into the database, and it is case-sensitive, correct me if
>I'm wrong. So if we are to select a certain string from the database, we
>have to enter it exactly as the way it was entered for storing, for
>example, if the a string is stored in the database as "AbcDefGhi" when
>selecting that string, we will have to state exactly "AbcDefGhi", or the
>select will be a failure. Is this true?
>
>And is there any way to overcome this constraint so that the select of
>the string will be not be case-sensitive? As for my project, different
>users may have different style of entering information into the system.
>So for the person who is querying the database, it is impossible to know
>how the information was entered and stored in the database.
>
>We thought of converting everything into upper or lower case before
>storing, however, as the language prowess of my users are rather
>limited, it will be difficult for them to read and understand a sentence
>that is either in full upper or lower case.
>
>Thanks.
>
Received on Wed Feb 03 1999 - 06:44:20 CST