RE: sql DELETE FROM with wildcard "%"

Well, (BCITY || '%') probably won't do what you want because it appends the
percent sign to the end of the field, including blanks.  So if BCITY is 10A
and the contents are 'Aurora', you would get "Aurora___%" (where underscores
are blanks).  That won't match anything, especially if ACITY is the same
size.  I suspect you would actually want "Aurora%", which would require some
trimming.

Probably:

        FCITY = %trim(BCITY) + '%';

Then use FCITY in your SQL.  However, wildcards and variables have
traditionally had some quirks.  Personally, I have found that if I need to
use a variable to hold a wildcard, I need to do one of two things:

1. Make sure the variable is EXACTLY long enough to hold the exact length of
the value plus the percent sign.  This works if you know the length ahead of
time (in this case, you'd define BCITY as 7A).

2. Pad the entire field with percent signs.  One of the ways to do this
would be as follows:

        FCITY = *ALL'%';
        FCITY = %trim(BCITY) + FCITY;

Joe

From: fkany@xxxxxxxxxxxxxxxxxx

Hello,

Is this the correct way to write an embedded sql "DELETE FROM" statement
with a wildcard?
==============================================================
           C/EXEC SQL
           C+ delete from qtemp/tempf1 where ACITY not like :BCITY || '%'
           C/END-EXEC
==============================================================