Substring is a common enough function in SQL, however, the exact language format used to perform this function can vary from one database to another. So, here are a few quick notes on the substring format in Netezza / PureData.
What is the purpose of a substring?
SUBSTRING allows SQL to extract part of a string with a field, which in Netezza / PureData is based on positions with the string.
Substring Function Syntax
Substring(<<Input Field>>)from <<start-position>> [for <<length in Characters>>]).
Notes:
· Square brackets ‘[ ]’ indicate optional content, which can be useful under certain circumstances
· If you do not specify the optional for length [for <<length in Characters>>], then the remained of the string will be returned
Example Substring SQL Using ‘For Length’ property
Netezza / Puredata Substring Function
Example Substring SQL
SELECT SUBSTRING(DD.DATE_SRKY FROM 1 FOR 4) as First_Four_Digits
, SUBSTRING(DD.DATE_SRKY FROM 5 FOR 2) as Middle_Two_Digits
, SUBSTRING(DD.DATE_SRKY FROM 7 FOR 2) as Last_Two_Digits
,DD.DATE_SRKY
FROM DATE_DIM DD
Order by DD.DATE_SRKY DESC
Example Substring SQL without the optional ‘For Length’ property
Netezza String Function Without For Length Property
Example Substring SQL
SELECT SUBSTRING(DD.DATE_SRKY FROM 5 ) as Remainder_String_From_Position_5
, SUBSTRING(DD.DATE_SRKY FROM 7 ) as Remainder_String_From_Position_7
,DD.DATE_SRKY
FROM DATE_DIM DD
Order by DD.DATE_SRKY DESC
Example Substring SQL which bring the parts together in a new format
Netezza / Puredata Substring Function Reassembled As Date
Example Substring SQL
SELECT SUBSTRING(DD.DATE_SRKY FROM 1 FOR 4)||’-‘||
SUBSTRING(DD.DATE_SRKY FROM 5 FOR 2)||’-‘||
SUBSTRING(DD.DATE_SRKY FROM 7 FOR 2) || ‘ 00:00:00’ as Reassembled_As_Date_Format
