Chat now with support
Chat with Support
• Get Live Help Toad Data Point 5.3 - Installation Guide

ToChar Date and Numeric Functions

When using the ToChar function, you must use the .Net format strings. The following are some examples:

Format Example
MM/dd/yyyy 08/26/2011
dddd, dd MMMM yyyy Tuesday, 26 August 2011
dddd, dd MMMM yyyy h:mm tt Tuesday, 26 August 2011 6:30 AM
MM/dd/yyyy h:mm tt 08/26/2011 6:30 AM
dddd, dd MMMM yyyy HH:mm:ss Tuesday, 26 August 2011 06:30:07
MM/dd/yyyy HH:mm:ss 08/26/2011 06:30:07
#### 1234
#.## 1.23
0.00 1.23
#,# 1,234,456
(###) ### - #### (123) 456 - 7890

Notes:

• Aggregate functions, such as Avg, cannot be used as parameters in transformation functions. To work around this, create a separate column containing output of the aggregate function and use this column as the parameter.
• In functions, you must use a period (.) as a decimal separator. You cannot use a comma as a decimal separator.

Math Functions

Math functions return a numeric value based on input values supplied as arguments. Review the following for additional information:

 Abs(number) Returns the absolute value of a number. Atan(number) Returns the arc tangent of a number as a numeric value expressed in radians. Ceiling(number) Returns the first integer that is greater than or equal to the specified number. Cos(number) Returns a number in radians specifying the cosine of an angle. Cosh(number) Returns the hyperbolic cosine of a specified number. Notes: If a specified number is Null, the result of this function is Null. If a specified number is zero (0), the result of this function is one (1.0). Count(field) Returns the number of rows retrieved from the field. Note: This function counts only those records in which the field is not Null. Exp(number) Returns a number specifying e. The value of e is approximately 2.718282. Tip: This function is useful to see a relationship in which a percentage increase or decrease in the independent variable has the same proportional change in the dependent variable. Floor(number) Returns the first integer that is less than or equal to the specified number. Hex(number) Returns hexadecimal representation of the number. Max(num1, num2) Returns the maximum of two expressions. Min (num1, num 2) Returns the minimum of two expressions. Power(number, n) Calculates the number of times an expression is multiplied by itself. Round(number) Returns a similar value of a number while reducing the number of digits. Sign(number) Returns the sign of the number (positive values are 1, negative values are -1, and 0 is 0). Sin(number) Returns a number in radians specifying the sine of an angle. Sinh(number) Returns the hyperbolic sine of a number. Sqrt(number) Returns the square root of a number. Tan(number) Returns a number in radians specifying the tangent of an angle. Tanh(number) Returns the hyperbolic tangent of a number. Trunc(number, precision) Returns a number truncated to specified precision.

String Functions

You can use string functions to manipulate, change, or edit the contents of a string. Review the following for additional information:

 Nvl(field,valueWhenNULL) Returns a substitute value if a value is null where field represents the position of the first argument in succeeding arguments, and valueWhenNull represents the value to replace the null values. If(expression, value, default) Returns a default value for a row where expression evaluates to the default value, value represents the default value, and default assigns the default value. Ascii(string) Returns the numeric value of the first left character in a string where string represents the string to convert. Concat(string1, string2) Joins one or more strings to the current string and returns the result where string1 represents the first string to join and string2 represents the second string to join. Initcap(string) Changes the first letter of a string to uppercase where string represents the string to manipulate. Instr(str1, str2, startPos,occurance) Determines if one string contains another and returns the position of the first occurrence of one string within another where str1 represents the string to be searched, str2 represents the string searched for in str1, and startPos represents where the search is to begin in str1. Length(string) Returns the length of a string where string represents the string to manipulate. Lower(string) Returns the entire string in lowercase where string represents the string to manipulate. Ltrim(str1,charsToRemove) Removes the leftmost leading characters in a string where str1 represents the string to manipulate and charsToRemove represent characters in the trim list. RemovePrefix(str1,str2) Removes the leftmost leading characters in a string where str1 represents the string to manipulate and str2 represents the string to remove. If str2 is not found on the leading characters, str1 is returned. For example, RemovePrefix('IntMyVariable','Int') returns: MyVariable. Replace(str1,str2,str3) Replaces occurrences of a string where str1, str2, and str3 represent the strings to manipulate. Rtrim(str1,charsToRemove) Removes the rightmost trailing characters in a string where str1 represents the string to manipulate and charsToRemove represent characters in the trim list. RemoveSuffix(str1,str2) Removes the rightmost trailing characters in a string where str1 represents the string to manipulate and str2 represents the string to remove. If str2 is not found on the trailing characters, str1 is returned. For example, RemoveSuffix('MyVariableEnd','End') returns: MyVariable. Substr(str1, start, length) Returns the substring of a string where str1 represents the string to manipulate, start represents the beginning of the string to manipulate, and length represents the length of the string to manipulate. Upper(String) Returns the entire string in uppercase where String represents the string to manipulate.

Statistical Functions

You can use statistical functions to collect statistical information about column data. Review the following for additional information:

 Median(number) Calculates the middle value in a column of numbers arranged in value order where number represents the column of numbers. If a column contains an even number of rows, the mean of the two middle values are calculated. Median(number, break) Calculates the middle value in a column of numbers arranged in value order where number represents the column of numbers, and break represents an optional break column. If a column contains an even number of rows, the mean of the two middle values is calculated. Mode(number) Calculates the value that occurs most frequently in a column of numbers where numbers represents the column of numbers. Mode(number, break) Calculates the value that occurs most frequently in a column of numbers where numbers represents the column of numbers, and break represents an optional break column. Note: Mode includes values of zero (0) and ignores null values. If there are no duplicate values, the value of the first cell is returned. Percentile(numbers, n) Measures the nth percentile of the total frequency of values in a column of numbers where numbers represents the column of numbers, and n represents the percentile value zero to one. Percentile(numbers, n, break) Measures the nth percentile of the total frequency of values in a column of numbers where numbers represents the column of numbers, n represents the percentile value zero to one, and break represents an optional break column. Tip: You can also measure quartile values. Lower quartile - set nth value to .25% to remove lowest 25% of data. Interquartile - set the nth value to .5 for spread of the middle 50% of data. Upper quartile - set the nth value to .75 to remove the highest 25% of data. Rank(numbers) Calculates the sequence of numbers in a column in descending order where numbers represents the column of numbers. Rank(numbers, break) Calculates the sequence of numbers in a column in descending order where numbers represents the column of numbers, and break represents an optional break column. Note: Duplicate numbers have the same ranking and affect the ranking of subsequent numbers. RankAsc(numbers) Calculates the sequence of numbers in a column in ascending order where numbers represents the column of numbers. RankAsc(numbers, break) Calculates the sequence of numbers in a column in descending order where numbers represents the column of numbers, and break represents an optional break column. Note: Duplicate numbers have the same ranking and affect the ranking of subsequent numbers. GeometricMean(number) Returns an average calculated by multiplying all numbers in a set and taking the nth root of the total where number is the number of numbers. GeometricMean(number, break) Returns an average calculated by multiplying all numbers in a set and taking the nth root of the total where number is the number of numbers, and break represents an optional break column. Note: The geometric mean formula might produce large numbers. StdDev(number) Calculates the dispersion of values in a sampling of the data set from its average value (mean) where number represents the column of numbers. The more spread out the data, the higher the deviation. Tip: StdDev uses a sampling of the data to calculate standard deviation. Use StdDevp for the entire data set. Notes: An error is returned if the result set contains one row or less of data. The nonbiased or n-1 method is used to calculate standard deviation. StdDev(number, break) Calculates the dispersion of values in a sampling of the data set from its average value (mean) where numbers represents the column of numbers, and break represents an optional break column. Tip: StdDev uses a sampling of the data to calculate standard deviation. Use StdDevp for the entire data set. Notes: An error is returned if the result set contains one row or less of data. The nonbiased or n-1 method is used to calculate standard deviation. StdDevp(number) Calculates the dispersion of values in the entire data set from its average value (mean) where number represents the column of numbers. The more spread out the data, the higher the deviation. Tip: StdDevp uses the entire data set to calculate standard deviation. Use StdDev for a sampling of the data set. Note: The biased or n method is used to calculate standard deviation. StdDevp(number, break) Calculates the dispersion of values in the entire data set from its average value (mean) where number represents the column of numbers, and break represents an optional break column. The more spread out the data, the higher the deviation. Tip: StdDevp uses the entire data set to calculate standard deviation. Use StdDev for a sampling of the data set. Note: The biased or n method is used to calculate standard deviation. Var(number) Estimates variability drawn randomly from a sampling of data where number represents the column of numbers. Var(number, break) Estimates variability drawn randomly from a sampling of data where number represents the column of numbers, and break represents an optional break column. Tip: Var uses a sampling of the data to estimate variability. Use Varp for the entire data set. Varp (number) Estimates variability using the entire data set where number represents the column of numbers. Varp(number, break) Estimates variability using the entire data set where number represents the column of numbers, and break represents an optional break column. Tip: Varp uses the entire data set to estimate variability. Use Var for a sampling of the data set.

Trending Functions

A moving average is used to indicate a trend. It detects the start of a trend and follows its progress, including reporting its reversal. Review the following for additional information:

Related Topics

Related Documents