Board index » delphi » How to control 'Case-Sensitivity'?

How to control 'Case-Sensitivity'?

Can we control 'Case-Sensitivity' in a SQL statement?

Thanks in advance.

Happy coding!

 

Re:How to control 'Case-Sensitivity'?


Wisher <wis...@ms6.hinet.net> wrote ...

Quote
> Can we control 'Case-Sensitivity' in a SQL statement?

String comparisons in most implementations of SQL is case sensitive.
Depending on the implementation, entity (table, index, etc.) names may also
be case sensitive - in Oracle and Sybase for example.

If your intent is to search for data without regard to case, then use a
function that converts the columns you're comparing to upper or lower case
and then compare against an upper or lower case literal string.

For example, the following would work in InterBase.

        SELECT ... FROM ... WHERE UPPER(col1) = "ABC"

                or

        SELECT ... FROM ... WHERE UPPER(col1) STARTS WITH "ABC"

--
Roland Bouchereau

Re:How to control 'Case-Sensitivity'?


Quote
Roland Bouchereau wrote:

> Wisher <wis...@ms6.hinet.net> wrote ...
> > Can we control 'Case-Sensitivity' in a SQL statement?

> String comparisons in most implementations of SQL is case sensitive.
> Depending on the implementation, entity (table, index, etc.) names may also
> be case sensitive - in Oracle and Sybase for example.

> If your intent is to search for data without regard to case, then use a
> function that converts the columns you're comparing to upper or lower case
> and then compare against an upper or lower case literal string.

> For example, the following would work in InterBase.

>         SELECT ... FROM ... WHERE UPPER(col1) = "ABC"

>                 or

>         SELECT ... FROM ... WHERE UPPER(col1) STARTS WITH "ABC"

> --
> Roland Bouchereau

This solution can be sufficient for a batch processing but there is one
serious drawback: Many SQL servers won't use an existing index on col1
because indexes are case sensitive and col1 is an argument of a function
in the where clause. So select will invoke a table scan and will be
slow on a large table.

The workaround I know is simple: add another column to the table called
scol1 for the case insensitive search. Create a trigger for insert and
update what stores UPPER(col1) to scol1, create an index for scol1 then
use scol1 for searching purposes and update col1.

This doubles the space required for that column but can be used for
other
purposes too: for example you can TRIM col1, cut off the spaces and
other
confusing things from it to make the search more reliable. Just don't
forget
to do the same formatting in the search string too. Use this method on
the
most important search fields only !
--
Best: Andrew

-----------------------------------------------------------------------------------------------
ATTN: Remove the .remove from the end of return address. It is to
prevent me from junk mailers.
-----------------------------------------------------------------------------------------------

Other Threads