Character string functions

The following table describes the IBM® Netezza® functions for manipulating character strings.

Note: The Netezza SQL string comparison ignores trailing spaces.
Table 1. Character functions
Name Description
ascii(s) Returns the numeric ASCII value of the first character in the specified string. For the NCHAR version of this function, see unicode(s).
btrim(s) Trims spaces from both ends of the specified string.
btrim(s,t) Trims occurrences of the characters in string t from both ends of string s.
chr(n) Returns the character with the specified ASCII value. For the NCHAR version of this function, see unichar(n).
initcap(s) Capitalizes the first character of each word of the specified string.
instr(s1,s2[,n[,m]]) Returns the location of substring s2 in string s1. The function also supports nchar and nvarchar character strings. The following arguments are optional:
n
The position from which to begin the search. When n is:
  • Positive, this is the position counting from left to right, and the search is conducted from left to right.
  • Negative, this is the position counting from right to left, and the search is conducted from right to left.
The default is 1.
m
Which occurrence of the substring is to be searched for. The default is 1 (to search for the first occurrence).

The return value is an integer that specifies the position in string s1 of substring s2 .

length(s) Returns the length of the specified string.
lower(s) Converts the specified string to lowercase.
lpad(s,n[,t]) Pads the left side of string s with characters to create a new string of length n. The optional argument t specifies the padding character; the default is a blank. If n is less than the length of s, the system truncates s to the specified length.
ltrim(s) Trims spaces from left end of the specified string.
ltrim(s,t) Trims occurrences of the characters in t string from the left end of string s.
repeat(s,n) Repeats string s n times. If the resulting string is greater than the maximum varchar length of 64,000 characters, it is truncated to 64,000 characters.
rpad(s,n) Spaces pad string s on right to length n. There is an optional third argument (t) that specifies the pad char. If the length argument is shorter than the string that is padded, the system truncates the string to the specified length.
rtrim(s) Trims spaces from right end of string s.
rtrim(s,t) Trims occurrences of the characters in t string from right end of string s.
strpos(s,b) Specifies starting position of substring b in string s.
substr(s,p,l) Returns a substring of string s that begins at position p and is l characters long. Position 1 is the beginning of the string. For example:
  • select substr('abcdefg',1,4); returns abcd.
  • select substr('abcdefg',4,1); returns d.
If the specified length causes the substring to exceed the bounds of the original string, positions outside the original string are ignored. For example:
  • select substr('abcdefg',3,9); returns the five-character substring cdefg.
  • select substr('abcdefg',9,3); returns a null string, that is, a string of length 0.
When the specified start position p is zero or negative, the ansi_substring runtime parameter determines the behavior of the substr function:
ansi_substring=true
When ansi_substring=true (this is the default setting):
  • Position 0 is one position to the left of position 1.
  • A negative start position is counted to the left from position 0. The substring includes the characters that continue to the right for the specified length.
For example:
  • select substr('abcdefg',0,4); returns abc
  • select substr('abcdefg',-2,4); returns a
  • select substr('abcdefg',-5,8); returns ab
ansi_substring=false
When ansi_substring=false:
  • Position 0 is equivalent to position 1.
  • A negative start position is counted from the rightmost character of the string to the left. The substring includes the characters that continue to the right for the specified length.
For example:
  • select substr('abcdefg',0,4); returns abcd
  • select substr('abcdefg',-2,4); returns fg
  • select substr('abcdefg',-5,8); returns cdefg
translate(s,from,to) Replaces any character in s that matches a character in the from set with the corresponding character in the to set. For example, translate(‘12345’,’14’,’ax’) returns ‘a23x5’.
upper(s) Converts string s to uppercase.
unichr(n) Returns the character with the specified ASCII value. Equivalent to the chr() function. The function verifies that the code points are in the valid ranges, and displays an error if the code points are in the invalid range of U+D800-U+DFFF or in decimal 55,296 - 57,343.
unicode(s) NCHAR version of ascii(). Returns the Unicode value of the first character in the string s. A separate function is defined because six characters have different values between Latin9 and Unicode.
unicodes(s,unit,base) Returns the Unicode value for every character in the string s. By default, if you specify only the string, the function returns the representation in UTF-32 hex digits. The unit value specifies 8, 16, or 32 to return the value in UTF-8, UTF-16, or UTF-32 protocol. The base value specifies ‘oct’, ‘dec’, or ‘hex’ in uppercase or lowercase (or 8, 10, or 16) to control the number base.