SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-34
LIKE Predicate
Using LIKE With CHARACTER Columns
Columns of data type CHARACTER are fixed length. If the string stored in
CHARACTER data columns is shorter than the length of the column, the string is
padded with blanks for the length of the column. For example, if you insert “Joe” into a
CHAR(6) column, the stored value becomes “Joe ”, which is padded with three
blank characters at the end of the string. In this example, the column FIRST_NAME
contains the value “Joe ”.
SELECT FIRST_NAME FROM NAMES
WHERE FIRST_NAME LIKE "Joe" ;
The query does not result in a match because of the column value’s blank padding. For
information on how to force a match in similar instances, see Using LIKE With TRIM on
page 1-35.
Using LIKE With Varying-Length Character Columns
Columns of varying-length data types do not include trailing blanks unless you specify
trailing blanks when you enter the values. For example, if you enter “Joe” into a
VARCHAR(4) column, the column contains “Joe”, with no padded blanks. In the
previous example, if FIRST_NAME were a VARCHAR column containing “Joe”, the
result would be a match.
Using LIKE With Wild-Card Characters
The LIKE predicate becomes more useful when you use wild-card characters.
Wild-card characters allow SQL to select values that include any characters, instead of
specific characters. For example, “Joe%” contains the wild-card character %. The %
character represents 0 or more characters of any value. By specifying “Joe%” as the
pattern for SQL to match, you are directing SQL to select all values that start with “Joe”
and end with any or no characters. In this example, “Joey”, “Joellen”, and “Joe” are
matches for “Joe%”:
SELECT FIRST_NAME FROM NAMES
WHERE FIRST_NAME LIKE "Joe%" ;
The underscore (_) wild-card character signifies that any single character is
acceptable. For example, “Joe_” matches “Joey” but not “Joellen” or “Joe”.
You can use more than one wild-card character. For example, “%Joe%” indicates a
string that contains the string “Joe”, regardless of the characters or number of
characters that precede or follow “Joe”.
Wild-card characters work on columns of fixed or varying length.
Be sure to specify wild-card characters (percent and underscore) in the character set
associated with the column or you might receive unexpected results. The SQL/MP
Reference Manual specifies the values for the character sets SQL supports.