469,949 Members | 2,631 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,949 developers. It's quick & easy.

Non-Case-Sensitve LIKE Operator

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
Oct 26 '06 #1
3 13165
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
Oct 26 '06 #2
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
Oct 26 '06 #3
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.

Oct 26 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by lothar | last post: by
25 posts views Thread by Yves Glodt | last post: by
32 posts views Thread by Adrian Herscu | last post: by
22 posts views Thread by Steve - DND | last post: by
399 posts views Thread by =?UTF-8?B?Ik1hcnRpbiB2LiBMw7Z3aXMi?= | last post: by
12 posts views Thread by puzzlecracker | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.