The POSITION function is used to return a number that represents the starting location of a specified character string with character data. To use the POSITION function, you must specify two pieces of information. First, pass it the name of the column containing the data to examine and second, the character string that it should look for within the data.
The function returns a single numeric value that points to the location of the first occurrence of the character string in the data. If the character string is not found, the function returns a zero to indicate that the string does not exist. This is important to remember: a zero means the character string was not found!
Since POSITION returns a single value, it does not indicate all locations or the number of times the search string might occur in the data. It only shows the first. Multiple POSITION functions and one or more SUBSTRING functions are required to do this type of search. An example of this is shown at the end of this chapter.
The syntax of the POSITION follows:
POSITION ( <character-string> IN <column-name> )
It is possible to use the POSITION function in the WHERE clause as well as in the SELECT list. This provides the ability to return data based on the presence or absence of a character string within the data.
The following is an example of using the POSITION function in a SELECT; it uses a column called Alphabet that contains all 26 letters, A through Z:1 Row Returned (in Teradata Mode) 1 Row Returned (in Teradata Mode)
Notice that the Find_m in Teradata mode returns the value of 13. Yet, in ANSI mode, it returns 0 because it was not found. Remember, ANSI is case specific and it considers ‘m’ and ‘M’ different characters.
You may notice that using the POSITION function in the WHERE clause works the same as the LIKE comparison that we used earlier in this book. The good news is that it works as fast as the LIKE and there is no need to use the wildcard characters. At the same time, it does not have the ability to use wildcard characters to force the search to look in a certain location or for multiple non-consecutive characters, as does the LIKE.
The case sensitivity issue is applied in the WHERE clause just as in the SELECT list. Therefore, it is best to always code it as if ANSI is the default mode, or else force case by using either the UPPER or LOWER conversions.
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.