Compatibility: ANSI

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)POSITION function in a SELECT 1 Row Returned (in Teradata Mode)POSITION function in a SELECT

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.

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

Teradata Topics