IBM PureData System for Analytics, Version 7.1

Character functions

The following table describes the character functions.

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 strings that begin at position `p` and is the size of l characters. The ansi_substring configuration variable controls the behavior of the `substr()` built-in function and its start position when the value is negative. The variable has the following values: When ansi_substring = 1 (set to true), a negative start position causes the substring function to insert empty characters before the start of the string. The number of empty characters that are inserted is 1 plus the absolute value of the start position. This value is the default value. When ansi_substring = 0 (set to false), a negative start position value causes the string to start on the rightmost character of the string, and to include those characters that continue to the left for the specific length. A start position of 0 is treated as a start position of 1. The following is an example of ansi_substring usage. In all cases, one row is returned, though the result might vary: SELECT substr (‘left and right’, 1, 4): Result if True: left Result if False: left SELECT substr (‘left and right’, 0, 4): Result if True: lef Result if False: left SELECT substr (‘left and right’, -5, 1): Result if True: Result if False: r
`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.