Netezza / PureData – Substring Function On Specific Delimiter
Netezza / PureData – Substring Function On Specific Delimiter
The function Substring (SUBSTR) in Netezza PureData provides the capability parse character type fields based on position within a character string. However, it is possible, with a little creativity, to substring based on the position of a character in the string. This approach give more flexibility to the substring function and makes the substring more useful in many cases. This approach works fine with either the substring or substr functions. In this example, I used the position example provide the numbers for the string command.
Example Substring SQL
Netezza PureData Substring Function On Specific Character In String
Substring SQL Used In Example
select LOCATIONTEXT
,position(‘,’ in LOCATIONTEXT) as Comma_Postion_In_String
—without Adjustment
,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)) as Substring_On_Comma
—Adjusted to account for extra space
,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as Substring_On_Comma_Ajusted
,’==Breaking_Up_The_Sting==’ as Divider
— breaking up the string
,SUBSTRING(LOCATIONTEXT,1, position(‘ ‘ in LOCATIONTEXT)-1) as Beggining_of_String
,SUBSTRING(LOCATIONTEXT,position(‘ ‘ in LOCATIONTEXT)+1, position(‘ ‘ in LOCATIONTEXT)-1) as Middle_Of_String
,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as End_Of_String
3 thoughts on “Netezza / PureData – Substring Function On Specific Delimiter”