Unlike any other database, Netezza nzsql supports the pattern matching. You can search for the string by matching particular patterns. The Netezza LIKE statement expression is most commonly used in pattern matching.
The Netezza LIKE statement returns true if the string contained in the set if strings or table columns that are represented by the pattern. The Netezza NOT LIKE statement returns false if LIKE returns true and vise-versa.
Read:
- Netezza Phonetic Matching Functions and Examples
- Netezza Fuzzy String search Functions and Examples
- Search for String Pattern in Netezza Database: Google Like Search
- Netezza String Functions and Examples
Netezza LIKE Statement Patterns Matching
If the string does not contain any percentage sign or underscore, then pattern is the string itself, in this case LIKE acts as an equality operator.
‘abc’ LIKE ‘abc’ – True : Equality operation‘abc’ LIKE ‘a%’ – True : Pattern matching. Return everything that starts with ‘a’’abc’ LIKE ‘_b_’ – True : Any single character matching ‘abc’ LIKE ‘c’ – False: does not match any condition
LIKE pattern matches always include the entire string. To match a pattern anywhere within a string, the pattern must start and end with a percent sign.
Netezza LIKE statement Escape Characters
To match a literal underscore or percent sign without matching other remaining characters in the string, you must precede the respective character in pattern with the escape character.
The default escape character in the Netezza LIKE statement is the backslash character. You can also choose a different character by using the ESCAPE clause in the LIKE statement. In case if you want to match the escape character itself, enter two escape characters.
Netezza LIKE statement Examples
SELECT * FROM your_table WHERE col1 LIKE 'ab%; SELECT * FROM your_table WHERE col1 NOT LIKE 'ab%;
You can combine Netezza LIKE operator in multiple conditions:
SELECT * FROM your_table WHERE LOWER(col1) LIKE 'ab% or LOWER(col1) NOT LIKE 'cd%;
Escape Character Examples
SELECT * FROM Your_table WHERE col1 LIKE '%90\%%' SELECT * FROM Your_table WHERE col1 LIKE '%90#%%' escape '#'
It is also possible to select no escape character by entering ESCAPE ” (empty single quotation marks). In this case, there is no way to turn off the special meaning of underscore and percent signs in the pattern.
Netezza LIKE statement is case sensitive. Netezza does not support ILIKE which is not case sensitive search. You can use the UPPER () or LOWER () function to make LIKE or NOT LIKE not case sensitive.