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
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
FROM Blog.D_ZIPCODE
where STATE = ‘PR’
AND CITY = ‘REPTO ROBLES’
Related References
- Netezza / PureData – Position Function
- Netezza / PureData – Substring Function
- Netezza / PureData – Substring Function Example
- 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
3 thoughts on “Netezza / PureData – Substring Function On Specific Delimiter”