Character String Search (LIKE) Teradata

The LIKE is used exclusively to search for character data strings. The major difference between the LIKE and the BETWEEN is that the BETWEEN looks for specific values within a range. The LIKE is normally used when looking for a string of characters within a column. Also, the LIKE has the capability to use "wildcard" characters.

Character String Search (LIKE)

The next SELECT finds all rows that have a character string that begins with ‘Sm’:

SELECT *
FROM Student_Table
WHERE Last_Name LIKE 'sm%' ;
1 Row returnedSELECT finds all rows that have a character string that begins with ‘Sm’

The fact that the ‘s’ is in the first position dictates its location in the data. Therefore, the ‘m’ must be in the second position. Then, the ‘%’ indicates that any number of characters (including none) may be in the third and subsequent positions. So, if the WHERE clause contained: LIKE ‘%sm’, it only looks for strings that end in "SM." On the other hand, if it were written as: LIKE ‘%sm%’, then all character strings containing "sm" anywhere are returned. Also, remember that in Teradata mode, the database is not case sensitive. However, in ANSI mode, the case of the letters must match exactly and the previous request must be written as ‘Sm%’ to obtain the same result. Care should be taken regarding case when working in ANSI mode. Otherwise, case does not matter.

The ‘_’ wildcard can be used to force a search to a specific location in the character string. Anything in that position is considered a match. However, a character must be in that position.

The following SELECT uses a LIKE to find all last names with an "A" in the second position of the last name:

SELECT *
FROM Student_Table
WHERE Last_Name LIKE ('_a%' ) ;
2 Rows returnedSELECT uses a LIKE to find all last names with an

In the above example, the "_" allows any character in the first position, but requires a character to be there.

The keywords ALL, ANY, or SOME can be used to further define the values being searched. They are the same quantifiers used with the IN. Here, the quantifiers are used to extend the flexibility of the LIKE clause.

Normally, the LIKE will look for a single set of characters within the data. Sometimes, that is not sufficient for the task at hand. There will be times when the characters to search are not consecutive, nor are they in the same sequence.

The next SELECT returns rows with both an ‘s’ and an ‘m’ because of the ALL.

/* set session transaction BTET in BTEQ */

SELECT *
FROM Student_Table
WHERE Last_Name LIKE ALL ('%S%', '%m%' ) ;
3 Rows returnednext SELECT returns rows with both an ‘s’ and an ‘m’ because of the ALL

It does not matter if the ‘s’ appears first or the ‘m’ appears first, as long as both are contained in the string.

Below, ANSI is case sensitive and only 1 row returns due to the fact that the ‘S’ is uppercase, so Thomas and McRoberts are not returned:

/* set session transaction ANSI in BTEQ */

SELECT *
FROM Student_Table
WHERE Last_Name LIKE ALL ('%S%', '%m%' ) ;
1 Rows returnedset session transaction ANSI in BTEQ

If, in the above statement, the ALL quantifier is changed to ANY (ANSI standard) or SOME (Teradata extension), then a character string containing either of the characters, ‘s’ or ‘m’, in either order is returned. It uses the OR comparison.

This next SELECT returns any row where the last name contains either an ‘s’ or an ‘m’:

/* set session transaction ANSI in BTEQ */

SELECT *
FROM Student_Table
WHERE Last_Name LIKE ANY ('%s%', '%m%' ) ;
8 Rows returnedlike-any

Always be aware of the issue regarding case sensitivity when using ANSI Mode. It will normally affect the number of rows returned and usually reduces the number of rows.

There is a specialty operation that can be performed in conjunction with the LIKE. Since the search uses the "_" and the "%" as wildcard characters, how can you search for actual data that contains a "_" or "%" in the data?

Now that we know how to use the wildcard characters, there is a way to take away the special meaning and literally make the wildcard characters an ‘_’ and a ‘%’. That is the purpose of ESCAPE. It tells the PE to not match anything, but instead, match the actual character of ‘_’ or ‘%’.

The next SELECT uses the ESCAPE to find all table names that have an "_" in the 8th position of the name from the Data Dictionary.

SELECT Tablename
FROM DBC.tables
WHERE Tablename LIKE ('_ _ _ _ _ _ _ \_%') ESCAPE ''
AND Databasename = 'mikel' ;
2 Rows returned

Tablename _______

Student_Table
Student_Course_Table

In the above output, the only thing that matters is the ‘_’ in position eight because of the first seven ‘_’ characters are still wildcards.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics