Additional SQL Functions
New SQL functions ease application migration.
New numeric scalar functions include:
• round - Returns a numeric value, rounded to the specified length or precision
• ceiling - Returns smallest integer greater than or equal to the argument
• floor - Returns largest integer less than or equal to the argument
• truncate - Truncates x to y decimal places
• atan2 - Arctangent of angle defined by coordinate pair (x, y)
• acos(n) - Arccosine of cosine value n
• asin - Arcsine value of sine value n
• tan - Tangent value of angle n
• pi - Value of pi (ratio of the circumference of a circle to its diameter)
• sign - Returns -1 if n < 0, 0 if n = 0, +1 if n > 0
For further details, see the section Numeric Functions in the SQL Reference Guide.
New string scalar functions include:
• chr - Converts integer into corresponding ASCII code.
• ltrim - Returns a character expression with leading blanks removed
• rtrim - Returns a character string with trailing blanks removed
• lpad - Returns specified character string of specified length left-padded by blanks or copies of the second expression
• rpad - Returns specified character string of specified length right-padded by blanks or copies of the second expression
• replace - Replaces all occurrences of a specified string value with another string value
• byteextract - Returns the nth byte of the specified string.
Note: Byteextract can replace charextract, which now handles multi-byte characters and whose returned data type has changed from char(1) to varchar(4).
For further details, see the section String Functions Supported in the SQL Reference Guide.