RE: SQL Question

Real nice thanks Vern.  Haven't used Escape before.

(Funny thing is, I got a severe mail from ScanMail about my abusive language in 
my mail beow!)

Thanks,
Sudha

-----Original Message-----
From: Vern Hamberg [mailto:vhamberg@centerfieldtechnology.com]
Sent: Thursday, January 02, 2003 2:15 PM
To: midrange-l@midrange.com
Subject: RE: SQL Question


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
>