Character string functions

The following table describes the functions for manipulating character strings.

Note: The Netezza Performance Server 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.