# Calculator functions list
**Note:** Use the following table to learn about each calculator function available in the Calculator step:
| Function | Description | Required fields |
| ------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------- |
| **Set field to constant value A** | Creates a field with a constant value. | **A** |
| **Create a copy of field A** | Creates a copy of a field with the given field value. | **A** |
| **A + B** | A plus B. | **A** and **B** |
| **A - B** | A minus B. | **A** and **B** |
| **A \* B** | A multiplied by B. | **A** and **B** |
| **A / B** | A divided by B. | **A** and **B** |
| **A \* A** | The square of A. | **A** |
| **SQRT( A )** | The square root of A. | **A** |
| **100 \* A / B** | The percentage of A in B. | **A** and **B** |
| **A - ( A \* B / 100 )** | Subtracts B % of A. | **A** and **B** |
| **A + ( A \* B / 100 )** | Adds B % to A. | **A** and **B** |
| **A + B \*C** | Adds A and B times C. | **A**, **B**, and **C** |
| **SQRT ( A\*A + B\*B )** | Calculates (A2 + B2). | **A** and **B** |
| **ROUND ( A )** | Returns the closest integer to the argument. The result is rounded to an integer by adding 1/2, taking the floor of the result, and casting the result to type 'int', such that the result is equal to the value of the expression: floor (a + 0.5).**Note:** If you need the rounding method "Round half to even", use the following method `ROUND( A, B )` with no decimals (B=0). | **A** |
| **ROUND ( A, B )** | Rounds A to the nearest positive infinity number. This rounding method is known as "Round half to ceiling."**Note:** The rounding method prior to Pentaho 6.0 was the "Round half to even" method. If you need to use this rounding method, see [Rounding method for the Round (A, B) function](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview/calculator/troubleshooting-the-calculator-step/round-a-b-function) in [Troubleshooting the Calculator step](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview/calculator/troubleshooting-the-calculator-step). | **A** and **B** |
| **STDROUND( A )** | Rounds A to the nearest integer. The used rounding method is "Round half away from zero." It is also called standard or common rounding, or German mercantile rounding. | **A** |
| **STDROUND( A, B )** | Same rounding method used as in `STDROUND (A)` but with B decimals. | **A** and **B** |
| **CEIL( A )** | The ceiling function maps a number to the smallest following integer. | **A** |
| **FLOOR( A )** | The floor function maps a number to the largest previous integer. | **A** |
| **NVL( A, B )** | If A is not NULL, returns A, else B. Note that sometimes your variable will not be null, but an empty string. | **A** and **B** |
| **Date A + B Days** | Adds B days to Date field A. **Note:** Only integer values for B are supported. If you need non-integer calculations, please add a second calculation with hours. | **A** and **B** |
| **Year of date A** | Calculates the year of date A. | **A** |
| **Month of date A** | Calculates the month of date A. | **A** |
| **Day of year of date A** | Calculates the day of year (1-365). | **A** |
| **Day of month of date A** | Calculates the day of month (1-31). | **A** |
| **Day of week of date A** | Calculates the day of the week (1-7). | **A** |
| **Week of year of date A** | Calculates the week of year (1-54). | **A** |
| **ISO8601 Week of year of date A** | Calculates the week of the year ISO8601 style. | **A** |
| **ISO8601 Year of date A** | Calculates the year ISO8601 style. | **A** |
| **Byte to hex encode of string A** | Encodes bytes in a string to a hexadecimal representation. | **A** |
| **Hex to byte decode of string A** | Decodes bytes in a string from its hexadecimal representation (add a leading 0 when A is of odd length). | **A** |
| **Char to hex encode of string A** | Encodes characters in a string to a hexadecimal representation. | **A** |
| **Hex to char decode of string A** | Decodes a string from its hexadecimal representation (add a leading 0 when A is of odd length). | **A** |
| **Checksum of a file A using CRC-32** | Calculates the checksum of a file using CRC-32.**Note:** This function is ignored by Spark when you run the PDI transformation on the Spark engine. | **A** |
| **Checksum of a file A using Adler-32** | Calculates the checksum of a file using Adler-32. **Note:** This function is ignored by Spark when you run the PDI transformation on the Spark engine. | **A** |
| **Checksum of a file A using MD5** | Calculates the checksum of a file using MD5. **Note:** This function is ignored by Spark when you run the PDI transformation on the Spark engine. | **A** |
| **Checksum of a file A using SHA-1** | Calculates the checksum of a file using SHA-1. **Note:** This function is ignored by Spark when you run the PDI transformation on the Spark engine. | **A** |
| **Levenshtein Distance (source A and target B)** | Calculates the [Levenshtein Distance](https://en.wikipedia.org/wiki/Levenshtein_distance). | **A** and **B** |
| **Metaphone of A (phonetics)** | Calculates the [Metaphone](https://en.wikipedia.org/wiki/Metaphone) of A. | **A** |
| **Double metaphone of A (phonetics)** | Calculates the [Double Metaphone](https://en.wikipedia.org/wiki/Metaphone#Double_Metaphone)of A. | **A** |
| **Absolute value ABS ( A )** | Calculates the absolute value of A. | **A** |
| **Remove time from a date A** | Removes time value of A.**Note:** Daylight Savings Time (DST) changes in Sao Paulo and some other parts of Brazil at midnight 0:00. This practice makes it impossible to set the time to 0:00 at the specific date, when the DST changes from 0:00 to 1:00 am. So there is one date in one year in these regions where this function will fail with an "IllegalArgumentException: HOUR\_OF\_DAY: 0 -> 1" error. This issue does not occur in Europe, the US, and other regions where the time changes at 1:00 or 2:00 or 3:00 am. | **A** |
| **Date A - Date B (in days)** | Calculates difference, in days, between A date field and B date field. | **A** and **B** |
| **A + B + C** | A plus B plus C. | **A**, **B**, and **C** |
| **First letter of each word of a string A in capital** | Transforms the first letter of each word within a string. | **A** |
| **UpperCase of a string A** | Transforms a string to uppercase. | **A** |
| **LowerCase of a string A** | Transforms a string to lowercase. | **A** |
| **Mask XML content from string A** | Escapes XML content; replaces characters with '& values'. | **A** |
| **Protect (CDATA) XML content from string A** | Indicates an XML string is general character data, rather than non-character data or character data with a more specific, limited structure. The given string will be enclosed into ``. | **A** |
| **Remove CR from a string A** | Removes carriage returns from a string. | **A** |
| **Remove LF from a string A** | Removes linefeeds from a string. | **A** |
| **Remove CRLF from a string A** | Removes carriage returns/linefeeds from a string. | **A** |
| **Remove TAB from a string A** | Removes tab characters from a string. | **A** |
| **Return only digits from string A** | Outputs only digits (0-9) from a string from a string. | **A** |
| **Remove digits from string A** | Removes all digits (0-9) from a string. | **A** |
| **Return the length of a string A** | Returns the length of the string. | **A** |
| **Load file content in binary** | Loads the content of the given file (in field A) to a binary data type (e.g. pictures). **Note:** This function is ignored by Spark when you run the PDI transformation on the Spark engine. | **A** |
| **Add time B to date A** | Add the time to a date, returns date and time as one value. | **A** and **B** |
| **Quarter of date A** | Returns the quarter (1 to 4) of the date. | **A** |
| **variable substitution in string A** | Substitutes variables within a string. | **A** |
| **Unescape XML content** | Unescapes XML content from the string. | **A** |
| **Escape HTML content** | Escapes HTML within the string. | **A** |
| **Unescape HTML content** | Unescapes HTML within the string. | **A** |
| **Escape SQL content** | Escapes the characters in a String to be suitable to pass to an SQL query. | **A** |
| **Date A - Date B (working days)** | Calculates the difference between Date field A and Date field B (only working days Mon-Fri). | **A** and **B** |
| **Date A + B Months** | Add B months to Date field A. **Note:** Only integer values for B are supported. If you need non-integer calculations, please add a second calculation with days. | **A** and **B** |
| **Check if an XML file A is well formed** | Validates XML file input.**Note:** This function is ignored by Spark when you run the PDI transformation on the Spark engine. | **A** |
| **Check if an XML string A is well formed** | Validates XML string input. | **A** |
| **Get encoding of file A** | Provides a guess of the best encoding (UTF-8) for the given file.**Note:** This function is ignored by Spark when you run the PDI transformation on the Spark engine. | **A** |
| **DamerauLevenshtein distance between String A and String B** | Calculates the [Damerau-Levenshtein](https://en.wikipedia.org/wiki/Damerau%E2%80%93Levenshtein_distance) distance between strings. | **A** and **B** |
| **NeedlemanWunsch distance between String A and String B** | Calculates the [Needleman-Wunsch](https://en.wikipedia.org/wiki/Needleman%E2%80%93Wunsch_algorithm) distance between strings. | **A** and **B** |
| **Jaro similitude between String A and String B** | Calculates the [Jaro similarity](https://en.wikipedia.org/wiki/Jaro%E2%80%93Winkler_distance) coefficient between two strings. | **A** and **B** |
| **JaroWinkler similitude between String A and String B** | Calculates the[Jaro-Winkler distance](https://en.wikipedia.org/wiki/Jaro%E2%80%93Winkler_distance) between two strings. | **A** and **B** |
| **SoundEx of String A** | Encodes a string into a [Soundex](https://en.wikipedia.org/wiki/Soundex) value. | **A** |
| **RefinedSoundEx of String A** | Retrieves the [Refined Soundex](https://en.wikipedia.org/wiki/Soundex)code for a given string object | **A** |
| **Date A + B Hours** | Adds B hours to Date field. **Note:** Only integer values for B are supported. If you need non-integer calculations, please add a second calculation with minutes. | **A** and **B** |
| **Date A + B Minutes** | Adds B minutes to Date field. **Note:** Only integer values for B are supported. If you need non-integer calculations, please add a second calculation with seconds. | **A** and **B** |
| **Date A - Date B (milliseconds)** | Subtracts B milliseconds from Date field A | **A** and **B** |
| **Date A - Date B (seconds)** | Subtracts B seconds from Date field A. **Note:** Only integer values for B are supported. If you need non-integer calculations, please add a second calculation with milliseconds. | **A** and **B** |
| **Date A - Date B (minutes)** | Subtracts B minutes from Date field A. **Note:** Only integer values for B are supported. If you need non-integer calculations, please add a second calculation with seconds. | **A** and **B** |
| **Date A - Date B (hours)** | Subtracts B hours from Date field A. **Note:** Only integer values for B are supported. If you need non-integer calculations, please add a second calculation with minutes. | **A** and **B** |
| **Hour of Day of Date A** | Extracts the hour part of the given date. | **A** |
| **Minute of Hour of Date A** | Extracts the minute part of the given date. | **A** |
| **Second of Minute of Date A** | Extracts the second part of a given date. | **A** |
| **ROUND\_CUSTOM( A , B )** | Rounds A using a specific type of rounding mode indicated by B . B must be a number, and can be an integer or decimal value. If B is a decimal value, then it will convert to the floor value, such that '5.7' = 5 and '1.1' = 1. Rounding Modes:
- 0 - UP: Rounding mode to round away from zero.
- 1 - DOWN: Rounding mode to round towards zero.
- 2 - CEILING: Rounding mode to round towards positive infinity.
- 3 - FLOOR: Rounding mode to round towards negative infinity.
- 4 - HALF\_UP: Rounding mode to round towards "nearest neighbor" unless both neighbors are equidistant, in which case round up.
- 5 - HALF\_DOWN: Rounding mode to round towards "nearest neighbor" unless both neighbors are equidistant, in which case round down.
- 6 - HALF\_EVEN: Rounding mode to round towards the "nearest neighbor" unless both neighbors are equidistant, in which case, round towards the even neighbor.
- 7 - UNNECESSARY: Rounding mode to assert that the requested operation has an exact result, hence no rounding is necessary.
| **A** and **B** |
| **ROUND\_CUSTOM( A , B , C )** | Rounds B using a specific type of rounding mode indicated by C. Calculates using the same conditions as the ROUND\_CUSTOM( A , B ) function with the following exceptions:- Field C is the rounding mode to be used.
- Field B is the decimal value where the rounding is to take place. For example, if A is '1667.2342', B is '3', and C is '2', then 1667.2342 will round to 1667.235 since the CEILING rounding mode is applied which rounds up the third decimal value "4" to a "5".
| **A**, **B**, and **C** |
| **Date A + B Seconds** | Adds the number of milliseconds offset from the [Epoch](https://docs.oracle.com/javase/7/docs/api/java/util/Calendar.html#Epoch)of A to the same of B, where A and B are dates. | **A** and **B** |
| **Remainder of A / B** | Returns the remainder of dividing B into A. This remainder can be an integer or decimal value. For example, if A is '100' and B is '56', then the remainder is 44. If A is '2.5' and B is '2.3', then the remainder is 0.2. | **A** and **B** |