The function Substring (SUBSTR) in Netezza PureData provides the capability to 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’;
[…] Netezza / PureData – Substring Function Example […]
[…] Netezza / PureData – Substring Function Example […]
[…] Netezza / PureData – Substring Function Example […]