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
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;
[…] Netezza / PureData – Position Function […]
[…] Netezza / PureData – Position Function […]
[…] Netezza / PureData – Position Function […]