«

»

Feb 25

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 PostHow to get Case Sensitivity in Teradata

3 comments

  1. Rohit Verma

    Dear Admin,

    I have a problem regarding it. I have a text box which accepts all the characters then if i choose any character as escape character then it throws error if that character was already present in the input string.. is there any work around for this?

  2. Amarnath

    Hi Soumya,

    You can use like this

    SELECT TABLENAME
    FROM DBC.TABLES
    WHERE TABLENAME LIKE ‘EMPz_%’ ESCAPE ‘z’;

  3. sowmya

    can use ‘z_%’ instead of “@_% ESCAPE’@’ “

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>