var1= ‘abc_’ + ‘%’;
select name from table where name like :var1;

Is column “name” a variable-length field?

Tom

Two very basic items — from the SQL Reference:

• The LIKE predicate searches for strings that have a certain pattern. The pattern is specified by a string in which the underscore and percent sign have special meanings. Trailing blanks in a pattern are a part of the pattern.

Be aware the the “underscore” indicates a single-position wild-card character in the pattern. Any character in that position will match your pattern unless you include an “ESCAPE expression” clause.

Also, if name is not a variable-length field and var1 contains trailing blanks after the “%”, then the blanks can be part of the pattern too. I.e., if var1 contains three blanks after the “%”, then name can match the pattern when it has three trailing blanks.

This can be tricky stuff.

Tom

Since there are no responses, I’ll add that one common resolution is:

var1= ‘abc_’ + ‘%%%%%%’;

If var1 is a 6 character variable, the resulting value would be ‘abc_%%’ after truncation. No trailing blanks remain in the pattern, so the pattern doesn’t select values that have a blank at the end — the pattern allows any characters in the 5th and 6th positions.

And an ESCAPE clause might alter the SELECT statement to be:

select name from table where name like :var1 ESCAPE '+';

But that would mean that the pattern would need to be ‘abc+_%%’, and now var1 should be 7 characters long.

Having data values that can contain ‘wild’ characters can make selection tricky. Trying to select fixed-length columns using generic specifications in embedded SQL can be tricky.

Control over data values and use of variable length columns can help a lot.

Tom