RE: SQL Question

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Hi, Sudha

This is not exactly intuitive. The reference manual says, when talking
about parameter markers:

> Note: When the pattern specified in a LIKE predicate is a parameter
> marker, and a fixed-length character host variable is used to replace the
> parameter marker; specify a value for the host variable that is the
> correct length. If you do not specify the correct length, the select will
> not return the intended results.
>
> For example, if the host variable is defined as CHAR(10), and the value
> WYSE% is assigned to that host variable, the host variable is padded with
> blanks on assignment. The pattern used is ('WYSE%     '). This pattern
> requests the database manager to search for all values that start with
> 'WYSE' and end with'      '. If you intended to search for only the values
> that start with 'WYSE' you should assign the value 'WSYE%%%%%%' to the
> host variable.
Seems that variable-length host variables might work fine.

Another aspect, little used, is the ESCAPE parameter, which lets you use
the wildcard characters in the string. The following shows how, so that a
'+' preceding a '%' or '_ means to treat those as real characters, not
wildcards.

C1 LIKE 'AAAA+%BBB%' ESCAPE '+'

Cool

Vern

At 01:15 PM 1/2/2003 -0600, you wrote:
> I have seen this too.  I have had to pad the host variable field  with
> *all(%) for LIKe keyword and we are in v5r1.
> Thanks,
> Sudha
>
> -----Original Message-----
> From: Smith, Graham [mailto:GSmith2@scj.com]
> Sent: Thursday, January 02, 2003 11:53 AM
> To: 'midrange-l@midrange.com'
> Subject: RE: SQL Question
>
>
> Rick,
>
> I wish you were right but I've bitter experience behind me in this case.
>
> Stumbled across this years back and found that in a Host variable the single
> % didn't work correctly but padding full length of host variable worked
> fine.
>
> Go figure!
>
> Cheers
> Graham