IBM PureData System for Analytics, Version 7.1

Standard string functions

Use string functions to manipulate text strings.

The following table describes the standard string functions.

Table 1. Standard string functions
Function	Description
upper lower	Converts the letter case of the string. Uses the data specified in UnicodeData.txt. They do not use the conversion rules in the Unicode Consortium’s SpecialCasing rules (http://www.unicode.org/Public/ 6.0.0/ucd/SpecialCasing.txt).
trim	Removes leading or trailing occurrences of characters from a string. For example, `trim( {leading \| trailing \| both} [<character> \| ‘ ‘ ] from {<character-value>})`.
Position	Finds one string within another. The value 0 indicates not found. For example, `position(<character-value> in <character-value>)`.
Substring	Extracts one string from another. For example, `Substring(<character-value> from <start-position> [for <length>])`. The first character is at position 1. If you do not specify `<length>`, then the rest of `<character-value>` is implied. If `<start-position>` is beyond the end of `<character-value>`, then IBM® Netezza® SQL returns an empty string. If `<start-position>` is negative or zero, then the start is at an imaginary position before the first character of `<character-value>`.
Like/not like	Provides pattern matching comparisons. Netezza SQL supports the standard pattern characters: %, _ and the escape character by default. For more information about using like, see Pattern matching.
Character length	Character_length (`<character-value>`) returns the length of the string in `<character-value>`.

Note: The Netezza SQL string comparison ignores trailing spaces.

Feedback | Copyright IBM Corporation 2014 | Last updated: 2014-02-28