String Operators

This page summarizes string operators for streaming SQL, including concatenation and string pattern comparison. These let you combine and compare the strings.

Operator Unary / Binary Description Notes
|| B Concatenation Concatenation also applies to binary types
LIKE B SQL string pattern comparison <string> LIKE <like pattern> [ESCAPE <escape character>]
SIMILAR TO B Regular expression comparison <string> SIMILAR TO <regex> [ESCAPE <escape character>]

Concatenation

This operator is used to concatenate one or more strings:

Operation Result
‘SQL’ || ‘stream’ SQLstream
‘SQL’ || '' || ‘stream’ SQLstream
‘SQL’ || ‘stream’ || ' ' || ‘Incorporated’ SQLstream Incorporated
<col1> || <col2> || <col3> || <col4> <col1><col2><col3><col4>
‘SQL’ || cast(NULL as varchar(10)) NULL

Concatenating an empty (zero length) string '' is allowed. Concatenating a NULL value results in a NULL result.

LIKE patterns

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
<any other character> Matches only the exact same character

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%’ 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 current 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.

SIMILAR TO Table

Delimiter Character in pattern Effect Rule
parentheses ( ) ( seq ) Groups the seq (used for defining precedence of pattern expressions) 1
brackets [ seq ] Matches any single character in the seq 2
caret or circumflex [^seq] Matches any single character not in the seq 3
[seq1^seq2] Matches any single character in seq1 and not in seq2 4
dash <character1>-<character2> Specifies a range of characters between character1 and character2 (using some known sequence like 1-9 or a-z) 5
bar [seq1&124;seq2] Matches either seq1 or seq2 6
asterisk seq* Matches zero or more repitition of seq 7
plus seq+ Matches one or more repetitions of seq 8
braces seq{<number>} Matches exactly <number> of repetitions of seq 9
seq {<low number>,<high number>} Matches <low number> or more repetitions of seq, to a maximum of <high number> repetitions 10
questionmark seq? Matches zero or one instances of seq 11
underscore _ Matches any single character 12
percent % Matches any substring, including the empty string 13
character <any other character> Matches only the exact same character 14
NULL NULL If either operand is NULL, the result of the SIMILAR TO operation is UNKNOWN. 15
Non-alphanumeric Special Characters that special character must be preceded by an escape character defined using an ESCAPE clause specified at the end of the pattern. 16

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