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.
The next SELECT finds all rows that have a character string that begins with ‘Sm’:SELECT *
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 *
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 *
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 *
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 *
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
In the above output, the only thing that matters is the ‘_’ in position eight because of the first seven ‘_’ characters are still wildcards.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.