The function Substring (SUBSTR) in Netezza PureData provides the capability parse character type fields based on position within a character string.
Substring Functions Basic Syntax
SUBSTRING Function Syntax
SUBSTRING(<<CharacterField>>,<< StartingPosition integer>>, <<for Number of characters Integer–optional>>)
SUBSTR Function Syntax
SUBSTR((<>,<< StartingPosition integer>>, <>)
Example Substring SQL
Substring SQL Used In Example
SELECT LOCATIONTEXT
— From the Left Of the String
—
— Using SUBSTRING Function
,’==SUBSTRING From the Left==’ as Divider1
,SUBSTRING(LOCATIONTEXT,1,5) as Beggining_Using_SUBSTRING_LFT
,SUBSTRING(LOCATIONTEXT,7,6) as Middle_Using_SUBSTRING_LFT
,SUBSTRING(LOCATIONTEXT,15) as End_Using_SUBSTRING_LFT
,’==SUBSTR From the Left==’ as Divider2
—Using SUBSTR Function
,SUBSTR(LOCATIONTEXT,1,5) as Beggining_Using_SUBSTR_LFT
,SUBSTR(LOCATIONTEXT,7,6) as Middle_Using_SUBSTR_LFT
,SUBSTR(LOCATIONTEXT,15) as End_Using_SUBSTR_LFT
—
— From the right of the String
—
,’==SUBSTRING From the Right==’ as Divider3
,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-18, 8) as Beggining_Using_SUBSTRING_RGT
,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-9, 6) as Middle_Using_SUBSTRING_RGT
,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-1) as End_Using_SUBSTRING_RGT
,’==SUBSTR From the right==’ as Divider4
,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-18, 8) as Beggining_Using_SUBSTR_RGT
,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-9, 6) as Middle_Using_SUBSTR_RGT
,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-1) as End_Using_SUBSTR_RGT
FROM BLOG.D_ZIPCODE
where STATE = ‘PR’
AND CITY = ‘REPTO ROBLES’;
Related References
- Netezza / PureData – Position Function
- Netezza / PureData – Substring Function
- Netezza / PureData – Row Number Function
- Netezza / PureData – SQL Cast Conversion to Integers
- Netezza / PureData – how to convert an integer to a date
- Netezza / PureData – Two ways to get Numeric Day of Year
- Netezza / PureData – How to convert an epoch field to a timestamp
- Netezza / PureData – How to calculate months between two dates
- Netezza / PureData – Substring Function On Specific Delimiter
- 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
