Re: Case Insesitive search (how)
In article <392AD02A.92B3A4A0@bloomberg.net>, Art S. Kagel
<kagel@bloomberg.net> writes
>Richard Krenek wrote:
>>
>> Hello all,
>> How do I do a case insesitive search. Lets say I have a field "This
>> Is A Test", I would like to seach for it using "this is a", (SELECT *
>> FROM table WHERE field LIKE '%this is a%' ) does not work. Any ideas?
>
>If you have IDS 7.3x or higher you can use the LOWER and/or UPPER functions:
>
>SELECT *
>FROM table
>WHERE lower(field) LIKE '%this is a%' ...;>
>But it will not be able to use any index on 'field'. If you have 8.2x or
>higher you can use functional indexes to index the 'field' using the LOWER
>or UPPER function results and the engine will use it for:
>
>SELECT *
>FROM table
>WHERE upper(field) LIKE upper('%this is a%') ...;>
>If you have IDS.2000/IIF.2000 you can create a UDI (User Defined Index) to
>accomplish this search.
>
Or just keep another column on the table which is in uppercase and
make searches use that field. Index the uppercase field and an index
will be used!
>Art S. Kagel
--
David Williams
Oh! Sure! Post the simple and obvious solution! Spoil sport!
Art S. Kagel
David Williams wrote:
>
> In article <392AD02A.92B3A4A0@bloomberg.net>, Art S. Kagel
> <kagel@bloomberg.net> writes
> >Richard Krenek wrote:
> >>
> >> Hello all,
> >> How do I do a case insesitive search. Lets say I have a field "This
> >> Is A Test", I would like to seach for it using "this is a", (SELECT *
> >> FROM table WHERE field LIKE '%this is a%' ) does not work. Any ideas?
> >
> >If you have IDS 7.3x or higher you can use the LOWER and/or UPPER functions:
> >
> >SELECT *
> >FROM table
> >WHERE lower(field) LIKE '%this is a%' ...;> >
> >But it will not be able to use any index on 'field'. If you have 8.2x or
> >higher you can use functional indexes to index the 'field' using the LOWER
> >or UPPER function results and the engine will use it for:
> >
> >SELECT *
> >FROM table
> >WHERE upper(field) LIKE upper('%this is a%') ...;> >
> >If you have IDS.2000/IIF.2000 you can create a UDI (User Defined Index) to
> >accomplish this search.
> >
>
> Or just keep another column on the table which is in uppercase and
> make searches use that field. Index the uppercase field and an index
> will be used!
>
> >Art S. Kagel
>
> --
> David Williams