Netezza / PureData – 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, 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
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;
Related References
- Netezza / PureData – Substring Function Example
- Netezza / PureData – Substring Function On Specific Delimiter
- Netezza / PureData – Substring Function
- Netezza / PureData – Casting Numbers to Character Data Type
- Netezza / PureData – SQL Cast Conversion to Integers
- Netezza / PureData – How to convert a timestamp to date in SQL
- Netezza / PureData – Now() Command For Current Date
- Netezza / PureData – Current Date Function
- Netezza / PureData – Substring Function
3 thoughts on “Netezza / PureData – Position Function”