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:
(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')
(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?
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% '
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% '
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?
Comment