ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How to use "LIKE" in RPG with both lowercase and uppercase character

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to use "LIKE" in RPG with both lowercase and uppercase character

    Hi all,
    I have program which use SQL in source. I use "LIKE.... %xx%", it runs ok, but it only shows the search results lowercase if I input lowercase character and other.

    I dont know how to show all cases include uppercase and lowercase in the search results.

    I use AS/SET tool of SSA Global Technologies Company.

    My code attached.

    Do "LIKE" SQL in DB2 separate between uppercase and lowercase characters? (Is it is difference with MS.Access, Oracle...?)

    Thanks for reply!
    Attached Files

  • #2
    There must be a function is SQL that does this

    But I will have to search:

    Until I find you could just use two variables in your select one in upper casr and one in lower.

    Translate lower to upper

    Code:
          *
          * Constants
          * ~~~~~~~~~
         D UpperCase         C                   'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
         D LowerCase       C                   'abcdefghijklmnopqrstuvwxyz'
          *
         C                   eval      XMLField = %xlate(LowerCase : UpperCase :
         C                                               %trim(xSRCDTA))
    Im actually working on this right now and should know better by the beginning of next week.

    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

    Comment


    • #3
      could you just use the UCASE (or UPPER) function? like:

      select * from thislib/thisfile where ucase(thisField) like ucase('%thisvalue%')?

      This way both search and compare are the same case always.

      Comment


      • #4
        Thank pjk very much. I add the "UCASE" function to my code as:
        Originally posted by pjk
        could you just use the UCASE (or UPPER) function? like:

        select * from thislib/thisfile where ucase(thisField) like ucase('%thisvalue%')?

        This way both search and compare are the same case always.
        And the result is very very ok.
        Thanks again for helping.

        Comment

        Working...
        X