The following notes apply to pad characters and how they are treated in strings:
- Pad characters are significant in both the character expression, and in the pattern string.
- When using pattern matching, be aware that both leading and trailing pad characters in the field or expression must match exactly with the pattern.
For example, ‘A%BC’ matches ‘AxxBC’, but not ‘AxxBCΔ’, and ‘A%BCΔ’ matches ‘AxxBCΔ’, but not ‘AxxBC’ or ‘AxxBCΔΔ’ (Δ indicates a pad character).
- To retrieve the row in all cases, consider using the TRIM function, which removes both leading and trailing pad characters from the source string before doing the pattern match.
For example, to remove trailing pad characters:
TRIM (TRAILING FROM expression) LIKE pattern-string
To remove leading and trailing pad characters:
TRIM (BOTH FROM expression) LIKE pattern-string
- If pattern_expression is forced to a fixed length, trailing pad characters might be appended. In such cases, the field must contain the same number of trailing pad characters in order to match.
For example, the following statement appends trailing pad characters to pattern strings shorter than 5 characters long.
CREATE MACRO (pattern (CHAR(5)) AS field LIKE :pattern…
- To retrieve the row in all cases, apply the TRIM function to the pattern string (TRIM (TRAILING FROM :pattern) ), or the macro parameter can be defined as VARCHAR.
These two methods do not always return the same results.TRIM removes pad characters, while the VARCHAR method maintains the data pattern exactly as entered.