Netezza Position Function

Technology - Netezza Position Function

The position function in Netezza is a simple enough function. It just returns the number of a specified character within a string (char, varchar, etc.) or zero if the character is not found. The real power of this command is when you imbed it with a character function in SQL, which requires a numeric response, but the character may be inconsistent from row to row in a field.

The Netezza POSITION function is used to find the starting position of a specified substring within a given string. The syntax of the POSITION function in Netezza is as follows:

The Position Function’s Basic Syntax

POSITION(substring IN string)

Here, substring is the substring that you want to search for, and string is the string in which you want to search for the substring.

The POSITION function returns an integer that represents the starting position of the first occurrence of the substring within the string. The function returns zero if the substring is not found in the string.

For example, let’s say you have a string ‘Netezza is a data warehousing appliance’. If you want to find the starting position of the word ‘data’ in this string, you can use the following query:

SELECT POSITION('data' IN 'Netezza is a data warehousing appliance');

This query will return the value 17, which is the starting position of the word ‘data’ within the given string.

Example Position Function SQL’s

Netezza PureData Position Function, SQL, Position Function
Netezza PureData Position Function

Position Function SQL Used in Example


,’==Postion Funtion Return Values==’ as Divider

,position(‘,’ in LOCATIONTEXT) as Postion_In_Nbr_String

,position(‘-‘ in LOCATIONTEXT) as Postion_Value_Not_Found

,’==Postion Combined with Substring Function==’ as Divider2

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as Position_Used_in_Substring_Function

FROM Blog.D_ZIPCODE  where STATE = ‘MN’ AND CITY = ‘RED WING’ limit 1;

Related References

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Netezza database user documentation, Netezza SQL command reference, Functions

IBM, Documentation, PureData System for Analytics, 7.2.1, Functions

IBM, Documantation, IBM Netezza Performance Server

%d bloggers like this: