The position function in Netezza is a simple enough function, it just returns the number of a specified character within a string (char, varchar, nvarchar, etc.) or zero if the character not found. The real power of this command is when you imbed it with character function, which require a numeric response, but the character may be inconsistent from row to row in a field.
The Position Function’s Basic Syntax
position(<<character or Character String>> in <<CharacterFieldName>>)
Example Position Function SQL
Netezza PureData Position Function
Position Function SQL Used in Example
select LOCATIONTEXT, CITY
,’==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;
3 thoughts on “Netezza / PureData – Position Function”