Bert Swope
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.
What is the purpose of a substring?
The purpose of a substring function in SQL is to extract a portion of a string value. In SQL, a string is a sequence of characters, and sometimes we need to extract a portion of the string to perform certain operations. The substring function allows us to extract a portion of the string based on a starting position and a length.
Here are some common use cases of the substring function in SQL:
- Data cleaning: The data format may not always be consistent when dealing with data from different sources. For example, a phone number field may have a country code prefix in some records and not in others. The substring function can extract the relevant portion of the string and standardize the data format.
- Data transformation: Sometimes, we need to transform a string value to a different format. For example, we may need to extract the year or month from a date field to perform aggregations. The substring function can extract the relevant portion of the date string.
- Data analysis: In some cases, we may need to analyze a portion of a string value. For example, we may need to count the number of occurrences of a specific substring within a larger string.
The substring function in SQL is a useful tool for working with string data and allows us to extract a portion of the string based on our specific needs.
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
The Netezza substring function is used to extract a substring from a given string based on the specified starting position and length. The syntax of the substring function in Netezza is as follows:
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 brings 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