Netezza / PureData – Substring Function

SQL (Structured Query Language), Netezza Puredata Substring Function, substr, substring
Netezza Puredata Substring Function

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
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
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
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

,DD.DATE_SRKY

FROM DATE_DIM DD

Order by DD.DATE_SRKY DESC

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions