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
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
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
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
,DD.DATE_SRKY
FROM DATE_DIM DD
Order by DD.DATE_SRKY DESC
Related References
- 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