RE: SQL Question

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

That's funny, Sudha. Maybe it's the word 'length', or the string 'wild', or
all those cuss word substitution characters!

;-)

Vern

At 04:25 PM 1/2/2003 -0600, you wrote:
> 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
> >