LIKE with ESCAPE sequence

LIKE is used in Teradata to match string against a set of pattern which a user required.

There are two wild card characters used in LIKE and they are – ‘_’ and ‘%’.

‘_’ is used for single character search.

‘%’ is used for multiple character search.

For e.g.

1) Suppose we want to search all the tables in Teradata dictionary whose name starts with EMP then our query will be –

SELECT TABLENAME

FROM DBC.TABLES

WHERE TABLENAME LIKE ‘EMP%’ ;

2) Suppose we want to search all tables whose fourth character is E then our query will be –

SELECT TABLENAME

FROM DBC.TABLES

WHERE TABLENAME LIKE ‘_ _ _E%’ ;

Escape Sequence in LIKE

Imagine a scenario in which we want to search all tables whose name start with EMP_.

Now we know that ‘_’ underscore is a wild card character in LIKE and we cannot use it directly in our query. In this type of scenarios ESCAPE sequence came for rescue.

SELECT TABLENAME

FROM DBC.TABLES

WHERE TABLENAME LIKE ‘EMP@_%’ ESCAPE ‘@’ ;

The result set for this query is –

EMP_TABLE, EMP_ID, EMP_DEPT etc.

In this query we defined ‘@’ as the ESCAPE sequence, anything after @ , will not be considered as the wild card but as a character itself.

LIKE ‘@_’ means the underscore is treated as an underscore, not as a wild card character.

LIKE ‘@%’ means the percent sign is treated as a percent sign, not as a wild card character.

LIKE ‘@@‘ means the two consecutive ‘@’ characters should be treated as a single ‘@’, not as an escape character.

The choice of the ESCAPE sequence totally depends on user. We can use any symbol or even a alphabet as the ESCAPE sequence.

For e.g.

SELECT TABLENAME

FROM DBC.TABLES

WHERE TABLENAME LIKE ‘EMP!_%’ ESCAPE ‘!’ ;

SELECT TABLENAME

FROM DBC.TABLES

WHERE TABLENAME LIKE ‘EMPA_%’ ESCAPE ‘A’ ;

SELECT TABLENAME

FROM DBC.TABLES

WHERE TABLENAME LIKE ‘EMP1_%’ ESCAPE ‘1’ ;

All these queries will fetch the same result set.

Related Post – How to get Case Sensitivity in Teradata