Non-Case-Sensitve LIKE Operator

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Michael Roppel
    #1

    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
    Tags: None
    • peteh
      #2
      Re: Non-Case-Sensitve LIKE Operator

      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

        Comment

        • Michael Roppel
          #3
          Re: Non-Case-Sensitve LIKE Operator

          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
          >

            Comment

            • Brian Tkatch
              #4
              Re: Non-Case-Sensitve LIKE Operator

              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.

                Comment

                Previous template Next
                Working...