ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL Select containing LIKE clause

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

  • SQL Select containing LIKE clause

    I have an SQL statement where I'm qualifying on a 6 char field using the LIKE clause. This is embedded in an RPG program, but it behaves the same way in interactive SQL.

    The data field contains values like: F1A, F2B, F3C, etc.

    If I run the following statement, i get no records:
    Code:
    select sum(mynumericfield)                                      
    from MYFILE                                            
    where MySixCharField like 'F%    '
    (there are 4 spaces after the % sign - total of 6 chars)

    If I run the same statement, removing ONE of the trailing spaces it works fine.

    If I run this statement, it works perfectly fine (returning everything that begins with 'ABC')
    Code:
    select sum(mynumericfield)                                      
    from MYFILE                                            
    where MySixCharField like 'ABC%    '
    (there are 2 spaces after the % sign - total of 6 chars)

    Normally, in interactive SQL you wouldn't have the spaces... but inside a program, I made my variable CHAR(6) to match the field size in the database file.

    Incidentally, good old Query does the same thing.

    Any idea what is going on?

  • #2
    if you are using the percent sign you don't need any additional spaces at all regardless of the field size in the table. just remove all blanks
    I'm not anti-social, I just don't like people -Tommy Holden

    Comment


    • #3
      Define your variable as varying length field instead of fixed length and remove all trailing blanks (as tom already recommended).
      You only need the trailing blanks if the data you are looking for end with exactly the number of blanks.

      Birgitta

      Comment


      • #4
        Originally posted by tomholden View Post
        if you are using the percent sign you don't need any additional spaces at all regardless of the field size in the table. just remove all blanks
        I understand that... but I'm loading an RPG variable inside a procedure... the value could be

        Comment


        • #5
          Originally posted by B.Hauser View Post
          Define your variable as varying length field instead of fixed length and remove all trailing blanks (as tom already recommended).
          You only need the trailing blanks if the data you are looking for end with exactly the number of blanks.

          Birgitta
          Actually, that is how I originally had it... the RPG Variable was VARCHAR(6) in the procedure interface.

          Turns out I had two issues... This works
          Last edited by gwilburn; September 14, 2018, 08:43 AM. Reason: Bonehead

          Comment

          Working...
          X