Non-Case-Sensitve LIKE Operator

[Michael Roppel]

If I do a
SELECT .... WHERE DTEXT LIKE '%Findit%'
on an iSeries, I won't get records with 'findit' because the LIKE Operator
is case sensitive.
Is there a way to do a non case sensitive selection with LIKE?

Thanks
Michael

[peteh]

Hi Micheal;
Try .... WHERE lcase(DTEXT) LIKE '%findit%' or alternatively
ucase(DTEXT) LIKE '%FINDIT%' (I'm assuming that these column functions
are supported on iSeries).

Note that you will do a table scan if that's the only search criteria
since the index can't be used.

Pete H

Michael Roppel wrote:

If I do a
SELECT .... WHERE DTEXT LIKE '%Findit%'
on an iSeries, I won't get records with 'findit' because the LIKE Operator
is case sensitive.
Is there a way to do a non case sensitive selection with LIKE?

Thanks
Michael

[Michael Roppel]

Hi Pete,
It works! Thank You!

peteh schrieb:

Hi Micheal;
Try .... WHERE lcase(DTEXT) LIKE '%findit%' or alternatively
ucase(DTEXT) LIKE '%FINDIT%' (I'm assuming that these column functions
are supported on iSeries).

Note that you will do a table scan if that's the only search criteria
since the index can't be used.

Pete H

Michael Roppel wrote:

>If I do a
SELECT .... WHERE DTEXT LIKE '%Findit%'
on an iSeries, I won't get records with 'findit' because the LIKE Operator
is case sensitive.
Is there a way to do a non case sensitive selection with LIKE?

Thanks
Michael

[Brian Tkatch]

peteh wrote:

Hi Micheal;
Try .... WHERE lcase(DTEXT) LIKE '%findit%' or alternatively
ucase(DTEXT) LIKE '%FINDIT%' (I'm assuming that these column functions
are supported on iSeries).

Note that you will do a table scan if that's the only search criteria
since the index can't be used.

Pete H

Michael Roppel wrote:

If I do a
SELECT .... WHERE DTEXT LIKE '%Findit%'
on an iSeries, I won't get records with 'findit' because the LIKE Operator
is case sensitive.
Is there a way to do a non case sensitive selection with LIKE?

Thanks
Michael

Note that you will do a table scan if that's the only search criteria
since the index can't be used.

If it is to be an often-run query, a GENERATED COLUMN can be used, and
i believe that can get the INDEX.

B.