This page summarizes string operators for streaming SQL, including concatenation and string pattern comparison. These let you combine and compare strings.
| Operator | Unary/Binary | Description | Notes | — | — | — |
| — | — | — | — |
| — | — |
| — | — | — |
| — | — |
| | | | B | Concatenation | Concatenation also applies to binary types.|
| SIMILAR TO | B | String pattern comparison |
This operator is used to concatenate one or more strings: | Operation | Result | | — | — | | ‘SQL’| | ‘stream’ | SQLstream| | ‘SQL’| | ‘stream’| | ‘ Incorporated’ | SQLstream Incorporated | |
LIKE compares a string to a string pattern. In the pattern, the characters _ (underscore) and % (percent) have special meaning:
| Character in pattern | Effect |
| — | — |
*_
*Matches any single character |
| *%
*Matches any substring, including the empty string |
| *
If either operand is NULL, the result of the LIKE operation is UNKNOWN. To explicitly match a special character in the character string, you must specify an escape character using the ESCAPE clause. The escape character must then precede the special character in the pattern. See examples below. | Operation | Result | | — | — | | ‘a’ LIKE ‘a’ | TRUE| | ‘a’ LIKE ‘b’ | FALSE| | ‘ab’ LIKE ‘a%’ | TRUE| | ‘ab’ LIKE ‘a\%’ ESCAPE ‘\’ | FALSE| | ‘a%’ LIKE ‘a\%’ ESCAPE ‘\’ | TRUE| | ‘a’ LIKE ‘a%’ | TRUE| | ‘abcd’ LIKE ‘a%’ | TRUE| | ‘1a’ LIKE ‘a’ | TRUE| | ‘123aXYZ’ LIKE ‘%a%’ | TRUE
### SIMILAR TO patterns SIMILAR TO compares a string to a pattern. It is much like the LIKE operator, but more powerful, as the patterns are regular expressions. *seq in the SIMILAR TO table below means any sequence of characters explicitly specified, such as ‘13aq’. Non-alphanumeric characters intended for matching must be preceded by an escape character explicitly declared in the SIMILAR TO statement, such as ‘13aq!’ SIMILAR TO ‘13aq!24br!% ESCAPE ‘\’ (This statement is TRUE). When a range is indicated, as when a dash is used in a pattern, the curent collating sequence is used. Typical ranges are 0-9 and a-z. This link provides a typical discussion of pattern-matching, including ranges. When a line requires multiple comparisons, the innermost pattern that can be matched will be matched first, then the “next-innermost,” etc. Expressions and matching operations that are enclosed within parentheses are evaluated before surrounding operations are applied, again by innermost-first precedence.
| Delimiter | Character in pattern | Effect | Rule | — | — | — |
| — | — | — | — |
| — | — |
| — | — | — |
| — | — |
| parentheses ( ) | ( - seq ) | Groups the - seq (used for defining precedence of pattern expressions) | 1 |
| | 2 |
| | 3 |
| | 4 |
| dash |
See examples below. | Operation | Result | Rule | — | — | — | | — | — | — | — | | — | — | | — | — | — | | — | — | | ‘a’ SIMILAR TO ‘a’ | TRUE | 14| | ‘a’ SIMILAR TO ‘b’ | FALSE | 14| | ‘ab’ SIMILAR TO ‘a%’ | TRUE | 13| | ‘a’ SIMILAR TO ‘a%’ | TRUE | 13| | ‘abcd’ SIMILAR TO ‘a%’ | TRUE | 13| | ‘1a’ SIMILAR TO ‘a’ | TRUE | 12| | ‘123aXYZ’ SIMILAR TO ‘%a%’ | TRUE | 13 & 12| | ‘abd’ SIMILAR TO ‘[ab][bcde]d’ | TRUE | 2| | ‘abd’ SIMILAR TO ‘[ab]d’ | FALSE | 2| | ‘cd’ SIMILAR TO ‘[a-e^c]d’ | FALSE | 4| | ‘yd’ SIMILAR TO ‘[^(a-e)]d’ | INVALID| | ‘fred’ SIMILAR TO ‘amyfred’ | TRUE | 6| | ‘acd’ SIMILAR TO ‘ab*c+d’ | TRUE | 7 & 8| | ‘abd’ SIMILAR TO ‘ab*c+d’ | FALSE | 7 & 8| | ‘abb’ SIMILAR TO ‘a(b{3})’ | FALSE | 9| | ‘abbbbb’ SIMILAR TO ‘a(b{3})’ | FALSE | 9| | ‘abbbbbbbb’ SIMILAR TO ‘ab{3,6}’ | FALSE | 10| | “ SIMILAR TO ‘(ab)?’ | TRUE | 11| | ‘a’ SIMILAR TO ‘(ab)?’ | FALSE | 11| | ‘ab’ SIMILAR TO ‘ab?’ | TRUE | 11| | ‘abb’ SIMILAR TO ‘ab?’ | FALSE | 11| | ‘ab’ SIMILAR TO ‘a\%’ ESCAPE ‘\’ | FALSE | 16| | ‘a%’ SIMILAR TO ‘a\%’ ESCAPE ‘\’ | TRUE | 16| | ‘a(b{3})’ SIMILAR TO ‘a(b{3})’ ESCAPE ‘\’ | TRUE | 16