Re: COBOL400-L Digest, Vol 4, Issue 59

There is a LIKE statement available in SQL 400 - the catch is that it
uses the % as a replacement character instead of the *.  What follows
below is from the SQL reference manual from the IBM document repository.

LIKE Predicate
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.
If the value of any of the arguments is null, the result of the LIKE
predicate is unknown.
The match-expression, pattern-expression, and escape-expression must
identify strings.
The values for match-expression, pattern-expression, and
escape-expression must either
all be binary strings or none can be binary strings. The three arguments
can
include a mixture of character strings and graphic strings.
None of the expressions can yield a distinct type. However, it can be a
function
that casts a distinct type to its source type.
If a sort sequence other than *HEX is in effect when the statement is
executed and
the LIKE predicate involves SBCS data or mixed data, the weighted values
of the
strings are compared instead of the actual values. The weighted values
are based
on the sort sequence.
With character strings, the terms character, percent sign, and
underscore in the
following discussion refer to single-byte characters. With graphic
strings, the terms
refer to double-byte or UCS-2 characters. With binary strings, the terms
refer to the
code points of those single-byte characters.
match-expression
An expression that specifies the string that is to be examined to see if
it
conforms to a certain pattern of characters.
LIKE pattern-expression
An expression that specifies the string that is to be matched.
A simple description of the pattern
The pattern is used to specify the conformance criteria for values in
the
match-expression where:
v The underscore sign (_) represents any single character.
v The percent sign (%) represents a string of zero or more characters.
v Any other character represents itself.
If the pattern-expression needs to include either the underscore or the
percent
character, the escape-expression is used to specify a character to
precede either
the underscore or percent character in the pattern.
A rigorous description of the pattern
This more rigorous description of the pattern ignores the use of the
escape-expression, which is covered the later.
 match-expression LIKE pattern-expression
NOT ESCAPE escape-expression

LIKE Predicate
Chapter 2. Language Elements 155
Let m denote a value of match-expression and p denote the value of
pattern-expression. The string p is interpreted as a sequence of the
minimum
number of substring specifiers, so each character of p is part of
exactly one
substring specifier. A substring specifier is an underscore, a percent
sign, or
any nonempty sequence of characters other than an underscore or a
percent
sign.
The result of the predicate is unknown if m or p is the null value;
otherwise,
the result of the predicate is either true or false. The result is true
either if both
m and p are empty strings, or there exists a partitioning of m into
substrings
such that:
v A substring of m is a sequence of zero or more contiguous characters
and
each character of m is part of exactly one substring.
v If the nth substring specifier is an underscore, the nth substring of
m is any
single character.
v If the nth substring specifier is a percent sign, the nth substring of
m is any
sequence of zero or more characters.
v If the nth substring specifier is neither an underscore nor a percent
sign, the
nth substring of m is equal to that substring specifier and has the same
length as that substring specifier.
v The number of substrings of m is the same as the number of substring
specifiers.
It follows that if p is an empty string and m is not an empty string;
the result is
false. Similarly, it follows that if m is an empty string and p is not
an empty
string consisting of other than percent signs, the result is false.
The predicate m NOT LIKE p is equivalent to the search condition NOT(m
LIKE p).
If necessary, the CCSID of the match-expression, pattern-expression, and
escape-expression are converted to the compatible CCSID between the
match-expression and pattern-expression.
Mixed data
If the expression is mixed data, the expression might contain
double-byte
characters, and the pattern can include both SBCS and DBCS characters.
In that
case the special characters in p are interpreted as follows:
v An SBCS underscore refers to one SBCS character.
v A DBCS underscore refers to one DBCS character.
v A percent sign (either SBCS or DBCS) refers to any number of
characters of
any type, either SBCS or DBCS.
v Redundant shifts in match-expression and pattern-expression are
ignored.29
UCS-2 data
If the expression is UCS-2 graphic data, the pattern can include either
or both
of the supported code points for the UCS-2 underscore and percent sign.
The
29. Redundant shifts are normally ignored. To guarantee that they are
ignored, however, specify the
IGNORE_LIKE_REDUNDANT_SHIFTS query attribute.
LIKE Predicate
156 DB2 UDB for iSeries SQL Reference V5R2
supported code points for the UCS-2 underscore are X'005F' and X'FF3F'.
The
supported code points for the UCS-2 percent sign are X'0025' and
X'FF05'.
Parameter Marker
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 a
correct
length is not specified, 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 five blank spaces. If you intended to search for
only
the values that start with 'WYSE' you should assign the value
'WYSE%%%%%%' to the host variable.
ESCAPE escape-expression
An expression that specifies a character to be used to modify the
special
meaning of the underscore (_) and percent (%) characters in the
pattern-expression. This allows the LIKE predicate to be used to match
values
that contain the actual percent and underscore characters. The following
rules
apply the use of the ESCAPE clause and the escape-expression:
v The escape-expression must be a string of length 1.30
v The pattern-expression must not contain the escape character except
when
followed by the escape character, percent, or underscore.
For example, if '+' is the escape character, any occurrences of '+'
other than
'++', '+_', or '+%' in the pattern-expression is an error.
v The escape-expression can be a parameter marker.
The following example shows the effect of successive occurrences of the
escape
character, which in this case is the plus sign (+).
When the pattern string is... The actual pattern is...
+% A percent sign
++% A plus sign followed by zero or more arbitrary
characters
+++% A plus sign followed by a percent sign
Examples
Example 1: Search for the string 'SYSTEMS' appearing anywhere within the
PROJNAME column in the PROJECT table.
SELECT PROJNAME
FROM PROJECT
WHERE PROJECT.PROJNAME LIKE '%SYSTEMS%'
Example 2: Search for a string with a first character of 'J' that is
exactly two
characters long in the FIRSTNME column of the EMPLOYEE table.
30. If it is NUL-terminated, a C character string variable of length 2
can be specified.
LIKE Predicate
Chapter 2. Language Elements 157
SELECT FIRSTNME
FROM EMPLOYEE
WHERE EMPLOYEE.FIRSTNME LIKE 'J_'
Example 3: In this example:
SELECT *
FROM TABLEY
WHERE C1 LIKE 'AAAA+%BBB%' ESCAPE '+'
'+' is the escape character and indicates that the search is for a
string that starts
with 'AAAA%BBB'. The '+%' is interpreted as a single occurrence of '%'
in the
pattern.
Example 4: Assume that a distinct type named ZIP_TYPE with a source data
type
of CHAR(5) exists and an ADDRZIP column with data type ZIP_TYPE exists
in
some table TABLEY. The following statement selects the row if the zip
code
(ADDRZIP) begins with '9555'.
SELECT *
FROM TABLEY
WHERE CHAR(ADDRZIP) LIKE '9555%'
Example 5: The RESUME column in sample table EMP_RESUME is defined as a
CLOB. If the host variable LASTNAME has a value of 'JONES', the
following
statement selects the RESUME column when the string JONES appears
anywhere
in the column.
SELECT RESUME
FROM EMP_RESUME
WHERE RESUME LIKE '%'||LASTNAME||'%'
Example 6: In the following table of EBCDIC examples, assume COL1 is
mixed
data. The table shows the results when the predicates in the first
column are
evaluated using the COL1 values from the second column:
LIKE Predicate

John A Arnold
(301) 354-2939
jarnold@xxxxxxxxxxxxx
 

-----Original Message-----
From: cobol400-l-bounces@xxxxxxxxxxxx
[mailto:cobol400-l-bounces@xxxxxxxxxxxx] On Behalf Of
cobol400-l-request@xxxxxxxxxxxx
Sent: Sunday, June 04, 2006 1:00 PM
To: cobol400-l@xxxxxxxxxxxx
Subject: COBOL400-L Digest, Vol 4, Issue 59

Send COBOL400-L mailing list submissions to
        cobol400-l@xxxxxxxxxxxx

To subscribe or unsubscribe via the World Wide Web, visit
        http://lists.midrange.com/mailman/listinfo/cobol400-l
or, via email, send a message with subject or body 'help' to
        cobol400-l-request@xxxxxxxxxxxx

You can reach the person managing the list at
        cobol400-l-owner@xxxxxxxxxxxx

When replying, please edit your Subject line so it is more specific
than "Re: Contents of COBOL400-L digest..."


Today's Topics:

   1.  Wild Card Processing (Joe Folorunso)


----------------------------------------------------------------------

message: 1
date: Sat, 3 Jun 2006 13:38:07 -0700 (PDT)
from: Joe Folorunso <jfolorunso@xxxxxxxxx>
subject: [COBOL400-L] Wild Card Processing

Is any one familiar with a method of processing wild card using
COBOL/400. Is it possible to use SQL/400 with embedded COBOL/400 or just
use the field in question as a partial list of a key field. Thanks


          Joe Folorunso
  Independent I.T. Consultant/Developer
  JAF GROUP INTERNATIONAL LLC
  Good name is better than Silver, Gold and Money






 __________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

------------------------------