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


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