Formula Functions

This page documents the list of functions that are supported in Formula Engine. Each of the functions provides examples of how that function can be used. Those examples can be inline edited to try out variations. You also try out any of these functions in Formula Playground.

Go through the Video Guide below for a short introduction on how to read this page. If you think any other formula is helpful, please let us know at support@datasert.com

info

All string functions are case-insensitive
While most of the functions are null tolerant, if there is a ? after parameter name, then it means that function is designed to handle the absence of that parameter
If there is a ... before a parameter name, it means you can specify multiple parameters separated by a comma

abbr

abbr(input, length)

Abbreviates the input to length chars.

If input is null, then returns as is
If input is string, then abbreviates the input. If string length is same or shorter than length then returns the input as is.
If input is longer than length, then truncates to length with ... appended.

Expression	Result
`abbr('Boing Inc', 5)`✏️	`Bo...`
`abbr('Boing Inc', 10)`✏️	`Boing Inc`

abs

abs(input)

Returns the absolute value of the input

If input is null, then returns the input as is
If input is a -ve number, then returns +ve of that number
If input is a +ve number, then returns the number as is
If input is not number, then return the input as is

Expression	Result
`abs(-10)`✏️	`10`
`abs(-10.45)`✏️	`10.45`
`abs(10)`✏️	`10`
`abs(10.45)`✏️	`10.45`
`abs(null)`✏️	`<null>`
`abs('John')`✏️	`John`

add

add(val: any, valToBeAdded: any): any

Adds valToBeAdded to val. The behavior depends on the type of val.

If val is null or undefined, returns null.
If valToBeAdded is null, undefined, or NaN, returns val unchanged.
If val is an array, it concatenates valToBeAdded to val.
Otherwise, adds valToBeAdded to val using a standard addition operation.

Expression	Result
`add([1, 2, 3], 4)`✏️	`[1,2,3,4]`
`add(5, 10)`✏️	`15`
`add(5, NaN)`✏️	`5`
`add(null, 5)`✏️	`<null>`
`add([], 10)`✏️	`[10]`

add_date

add_date(input: string, amount: number, unit: string)

Adds amount of date unit to given input

Valid unit values are years, quarters, months, weeks, days, hours, minutes, seconds, and milliseconds as well as its singular variants
amount can be -ve to subtract from the given input date
Input date must be in iso datetime or date format
Returns iso formatted datetime. If you want date formatted string, use format_date function

Expression	Result
`add_date('2020-01-01', 1, 'year')`✏️	`2021-01-01T00:00:00Z`
`add_date('2020-01-01', 4, 'month')`✏️	`2020-05-01T00:00:00Z`
`add_date('2020-01-01', 4, 'day')`✏️	`2020-01-05T00:00:00Z`
`add_date('2020-01-05T09:00:00', 4, 'hour')`✏️	`2020-01-05T13:00:00Z`
`add_date('2020-01-05T09:50:00', 10, 'minute')`✏️	`2020-01-05T10:00:00Z`
`add_date('2020-01-05T09:59:55', 10, 'second')`✏️	`2020-01-05T10:00:05Z`
`add_date('2020-01-05T09:09:55', 120, 'second')`✏️	`2020-01-05T09:11:55Z`

array_seq

array_seq(length: number, start = 0, step = 1): number[]

Generates an array of numbers based on a sequence.

Uses the sequence function to generate an array of a given length, starting from a specified value and incrementing by a step.
If the length exceeds 1000, it throws an error.

Expression	Result
`array_seq(5, 0, 1)`✏️	`[0,1,2,3,4]`
`array_seq(3, 2, 2)`✏️	`[2,4,6]`
`array_seq(1001)`✏️	`Error: Sequence supports generating max 1000 values but formula requested to generate 1001 values!`

avg

avg(...inputs)

Calculates the average of given inputs.

If any of the inputs are null or not-number, then it is treated as 0.

Expression	Result
`avg(10)`✏️	`10`
`avg(1, 2, 3)`✏️	`2`
`avg(10, 'John')`✏️	`5`
`avg(null)`✏️	`0`
`avg('John')`✏️	`0`

blank_if_null

If value is blank, returns null else returns value as is.

Expression	Result
`blank_if_null(null)`✏️	`<blank>`
`blank_if_null('')`✏️	`<blank>`
`blank_if_null('abc')`✏️	`abc`

blank_if_zero

If input is blank, then returns 0

Expression	Result
`blank_if_zero(0)`✏️	`<blank>`
`blank_if_zero('John')`✏️	`John`
`blank_if_zero(['','8','7'])`✏️	`["","8","7"]`

capital_case

capital_case(input)

Converts the first char of input to capital case and rest of the chars to lower case.

If input is null, then returns it as is.
If input is not string, then returns it as is.

Expression	Result
`capital_case(null)`✏️	`<null>`
`capital_case('John')`✏️	`John`
`capital_case('John MALANA')`✏️	`John malana`
`capital_case('john manalan')`✏️	`John manalan`
`capital_case(10)`✏️	`10`
`capital_case([1, 2])`✏️	`[1,2]`
`capital_case({})`✏️	`{}`

chop

chop(val: any, size?: number): any

Removes the last size elements from the given val. If size is not provided, it defaults to 1.

If val is null or undefined, the function returns null.
If the length of val is less than or equal to size, the function returns an empty slice of the same type.

Expression	Result
`chop('abcdef', 2)`✏️	`abcd`
`chop('abcdef')`✏️	`abcde`
`chop([1, 2, 3, 4], 2)`✏️	`[1,2]`
`chop([1, 2, 3, 4], 5)`✏️	`[]`
`chop(null, 2)`✏️	`<null>`

compare

compares two values and returns -1, 0 or 1 depending on if first value is less than, equal or greater than second number

Expression	Result
`compare(0,5)`✏️	`-1`
`compare(4,5)`✏️	`-1`
`compare(10,1)`✏️	`1`
`compare(10,9)`✏️	`1`
`compare('abc','abcd')`✏️	`-1`

concat_array

Takes one or more inputs and returns single array consisting of all inputs. If any of the input is array, then items of that array are included, not array itself.

contains

Checks if second value contains within the first value

Expression	Result
`contains('James Smith', 'Smit')`✏️	`true`
`contains('James Smith', 'Smith')`✏️	`true`
`contains('James Smith', 'Smitha')`✏️	`false`

dedupe

Dedupes the given array. If second parameter is specified, it can be string representing the field of included items or function which takes array item and returns the string to dedupe by.

diff_date

diff_date(inputDate: string, compareDate: string | null, unit: DateUnit): int

Compares the inputDate to compareDate and returns the difference between two given dates in whole integer values.

If inputDate is null then returns null
If compareDate is null, then uses the current datetime
If inputDate earlier than compareDate, then returns -ve integer value
If inputDate later than compareDate, then returns +ve integer value
If unit is null, then defaults to days
Valid unit values are
- years
- months
- days
- hours
- minutes
- seconds
- milliseconds

ends_with

ends_with(input, suffixes): boolean

Checks if input ends with any of the suffixes (case-insensitive) and returns true, otherwise returns false

suffixes could be single suffix or multiple suffixes. Multiple suffixes must be specified as an array of string.
If input is blank or suffixes is blank then returns false
Both input and suffixes are converted to string before checking

Expression	Result
`ends_with('James Smith', 'Smith')`✏️	`true`
`ends_with('James Smith', 'Smitha')`✏️	`false`
`ends_with('James Smith', ['t','h'])`✏️	`true`
`ends_with('James Smith', ['t','a'])`✏️	`false`
`ends_with('James Smith', 'h')`✏️	`true`
`ends_with(445, 5)`✏️	`true`
`ends_with(445, 3)`✏️	`false`
`ends_with(1234, 4)`✏️	`true`

ensure_prefix

ensure_prefix(value, prefix)

Ensures that given text has a specified prefix. If value does not start with given prefix, then prefix will be added else value is returned as is.

If input is an Array, then it operates on each element of Array.
Prefix check is case-insensitive

Expression	Result	Description
`ensure_prefix('123', 'ACCT-')`✏️	`ACCT-123`
`ensure_prefix('ACCT-123', 'ACCT-')`✏️	`ACCT-123`
`ensure_prefix('acct-123', 'ACCT-')`✏️	`ACCT-123`	If value starts with same prefix but different case, then prefix will be changed to specified case

ensure_suffix

ensure_suffix(value: any | any[], suffix: string): any | any[]

Ensures that given text has specified suffix. If value does not end with given suffix, then suffix will be added else value is returned as is.

If input is an Array, then it operates on each element of Array.
Suffix check is case In-sensitive

Expression	Result
`ensure_suffix('Boing', 'Inc')`✏️	`BoingInc`
`ensure_suffix('Boing Inc', 'Inc')`✏️	`Boing Inc`
`ensure_suffix('Boing inc', 'Inc')`✏️	`Boing Inc`

error

error(message)

Throws error with the given message. This function can be used in field or row mapping to mark record as an error. If Job is configured to treat Record errors as Error, then Job run will get Errored.

Expression	Result
`error('Simulating an error')`✏️	`Error: Simulating an error`

fill_array

fill_array(input: any[], fillValueOrFunction: (indexValue, index, array) => any)

Fills input array using value or function return value of fillValueOrFunction. If full function is not-function, then that value is used as is. Other wise fill function is called as many times as array length with three arguments viz

value at index, 2) index 3) array itself

If input or fillValueOrFunction is null then input is returned as is

Expression	Result
`fill_array(null)`✏️	`[]`

first

first(input, length)

If input is null, then returns the input as is.
If input is string, returns the first length chars of that string. If input is shorter than length, then input is returned as is.
If input is array, returns the first length elements of that array as new array. If input array length is shorter than length, then returns the input as is.

Expression	Result
`first('Mary John',4)`✏️	`Mary`
`first('Mary',3)`✏️	`Mar`
`first('Mary',5)`✏️	`Mary`

first_not_blank

From series of values, returns the first not blank value. If none of them are non-blank, then returns null

Expression	Result
`first_not_blank('Mary John','','John','Raj','','Mary')`✏️	`Mary John`
`first_not_blank('','John','Raj','','Mary')`✏️	`John`
`first_not_blank('','','')`✏️	`<null>`

first_not_null

From series of values, returns the first not null value. If none of them are non-null, then returns null

first_not_zero

From series of inputs, returns the first not zero value. If none of them are non-zero, then returns null

Expression	Result
`first_not_zero('','','','Raj','')`✏️	`<blank>`
`first_not_zero('0','0','0','0')`✏️	`0`
`first_not_zero('0','4','0','0')`✏️	`4`
`first_not_zero('3','4','0','0')`✏️	`3`
`first_not_zero('3','4','2','1')`✏️	`3`

flatten

flatten(input: T | T[], depth: number | undefined): T[]

Returns a new array with all input array elements concatenated into it single array recursively up to the specified depth, or infinite if depth not specified.

If input is null then returns empty array []
If there are null elements in the array, returns them in the array as is. If you want to remove those null elements, use filter_nulls method

Expression	Result
`flatten([['abc'], ['xyz']])`✏️	`["abc","xyz"]`
`flatten([['abc', ['abc2']], ['xyz', ['xyz2', ['xyz3']]]])`✏️	`["abc","abc2","xyz","xyz2","xyz3"]`
`flatten([['abc', ['abc2']], ['xyz', ['xyz2', ['xyz3']]]], 2)`✏️	`["abc","abc2","xyz","xyz2",["xyz3"]]`
`flatten([['abc'], ['xyz', null]])`✏️	`["abc","xyz",null]`
`flatten(null)`✏️	`[]`
`flatten([])`✏️	`[]`
`flatten('abc')`✏️	`["abc"]`

format_date

format_date(input, pattern, timeZone?)

Formats input date according to pattern in timeZone. This function uses Luxon formatting tokens.

If input is blank, then returns input without formatting
pattern can be either pre-defined string or series of formatting tokens. See below for list of supported pre-defiend formats as well as formatting tokens.
If pattern is blank, then it returns iso datetime string
If timeZone is blank, then defaults to UTC timeZone
List of valid Timezone Ids are documented here

Predefined Formats

Examples below uses 1983-10-14T13:07:04.054Z date/time formatted with America/New_York timezone. For ex., format_date('1983-10-14T13:07:04.054Z', 'datetime', 'America/New_York')

Expression	Result
`datetime`✏️	`1983-10-14T09:07:04.054-04:00`
`date`✏️	`1983-10-14`
`ms`✏️	`434984824054`
`secs`✏️	`434984824`
`date_short`✏️	`10/14/1983`
`date_med`✏️	`Oct 14, 1983`
`date_full`✏️	`October 14, 1983`
`date_huge`✏️	`Friday, October 14, 1983`
`time_simple`✏️	`9:07 AM`
`time_with_seconds`✏️	`9:07:04 AM`
`time_with_short_offset`✏️	`9:07:04 AM EDT`
`time_with_long_offset`✏️	`9:07:04 AM Eastern Daylight Time`
`time_24_simple`✏️	`09:07`
`time_24_with_seconds`✏️	`09:07:04`
`time_24_with_short_offset`✏️	`09:07:04 EDT`
`time_24_with_long_offset`✏️	`09:07:04 Eastern Daylight Time`
`datetime_short`✏️	`10/14/1983, 9:07 AM`
`datetime_med`✏️	`Oct 14, 1983, 9:07 AM`
`datetime_full`✏️	`October 14, 1983 at 9:07 AM EDT`
`datetime_huge`✏️	`Friday, October 14, 1983 at 9:07 AM Eastern Daylight Time`
`datetime_short_with_seconds`✏️	`10/14/1983, 9:07:04 AM`
`datetime_med_with_seconds`✏️	`Oct 14, 1983, 9:07:04 AM`
`datetime_full_with_seconds`✏️	`October 14, 1983 at 9:07:04 AM EDT`
`datetime_huge_with_seconds`✏️	`Friday, October 14, 1983 at 9:07:04 AM Eastern Daylight Time`

Formatting Tokens

Expression	Result	Description
`S`✏️	`54`	millisecond, no padding
`SSS`✏️	`054`	millisecond, padded to 3
`u`✏️	`054`	fractional seconds, functionally identical to SSS
`s`✏️	`4`	second, no padding
`ss`✏️	`04`	second, padded to 2 padding
`m`✏️	`7`	minute, no padding
`mm`✏️	`07`	minute, padded to 2
`h`✏️	`9`	hour in 12-hour time, no padding
`hh`✏️	`09`	hour in 12-hour time, padded to 2
`H`✏️	`9`	hour in 24-hour time, no padding
`HH`✏️	`09`	hour in 24-hour time, padded to 2
`Z`✏️	`-4`	narrow offset
`ZZ`✏️	`-04:00`	short offset
`ZZZ`✏️	`-0400`	techie offset
`ZZZZ`✏️	`EDT`	abbreviated named offset
`ZZZZZ`✏️	`Eastern Daylight Time`	unabbreviated named offset
`z`✏️	`America/New_York`	IANA zone
`a`✏️	`AM`	meridiem
`d`✏️	`14`	day of the month, no padding
`dd`✏️	`14`	day of the month, padded to 2
`E`✏️	`5`	day of the week, as number from 1-7 (Monday is 1, Sunday is 7)
`EEE`✏️	`Fri`	day of the week, as an abbreviate localized string
`EEEE`✏️	`Friday`	day of the week, as an unabbreviated localized string
`EEEEE`✏️	`F`	day of the week, as a single localized letter
`M`✏️	`10`	month as an unpadded number
`MM`✏️	`10`	month as an padded number
`MMM`✏️	`Oct`	month as an abbreviated localized string
`MMMM`✏️	`October`	month as an unabbreviated localized string
`MMMMM`✏️	`O`	month as a single localized letter
`y`✏️	`1983`	year, unpadded
`yy`✏️	`83`	two-digit year
`yyyy`✏️	`1983`	four- to six- digit year, pads to 4
`G`✏️	`AD`	abbreviated localized era
`GG`✏️	`Anno Domini`	unabbreviated localized era
`GGGGG`✏️	`A`	one-letter localized era
`kk`✏️	`83`	ISO week year, unpadded
`kkkk`✏️	`1983`	ISO week year, padded to 4
`W`✏️	`41`	ISO week number, unpadded
`WW`✏️	`41`	ISO week number, padded to 2
`o`✏️	`287`	ordinal (day of year), unpadded
`ooo`✏️	`287`	ordinal (day of year), padded to 3
`q`✏️	`4`	quarter, no padding
`qq`✏️	`04`	quarter, padded to 2
`D`✏️	`10/14/1983`	localized numeric date
`DD`✏️	`Oct 14, 1983`	localized date with abbreviated month
`DDD`✏️	`October 14, 1983`	localized date with full month
`DDDD`✏️	`Friday, October 14, 1983`	localized date with full month and weekday
`t`✏️	`9:07 AM`	localized time
`tt`✏️	`9:07:04 AM`	localized time with seconds
`ttt`✏️	`9:07:04 AM EDT`	localized time with seconds and abbreviated offset
`tttt`✏️	`9:07:04 AM Eastern Daylight Time`	localized time with seconds and full offset
`T`✏️	`09:07`	localized 24-hour time
`TT`✏️	`09:07:04`	localized 24-hour time with seconds
`TTT`✏️	`09:07:04 EDT`	localized 24-hour time with seconds and abbreviated offset
`TTTT`✏️	`09:07:04 Eastern Daylight Time`	localized 24-hour time with seconds and full offset
`f`✏️	`10/14/1983, 9:07 AM`	short localized date and time
`ff`✏️	`Oct 14, 1983, 9:07 AM`	less short localized date and time
`fff`✏️	`October 14, 1983 at 9:07 AM EDT`	verbose localized date and time
`ffff`✏️	`Friday, October 14, 1983 at 9:07 AM Eastern Daylight Time`	extra verbose localized date and time
`F`✏️	`10/14/1983, 9:07:04 AM`	short localized date and time with seconds
`FF`✏️	`Oct 14, 1983, 9:07:04 AM`	less short localized date and time with seconds
`FFF`✏️	`October 14, 1983 at 9:07:04 AM EDT`	verbose localized date and time with seconds
`FFFF`✏️	`Friday, October 14, 1983 at 9:07:04 AM Eastern Daylight Time`	extra verbose localized date and time with seconds
`X`✏️	`434984824`	unix timestamp in seconds
`x`✏️	`434984824054`	unix timestamp in milliseconds

Examples

Expression	Result
`format_date('1983-10-14T13:07:04.054Z', 'MM/dd/yyyy', 'America/New_York')`✏️	`10/14/1983`
`format_date('1983-10-14T13:07:04.054Z', 'dd/MM/yyyy', 'Europe/Lisbon')`✏️	`14/10/1983`
`format_date('1983-10-14T13:07:04.054Z', 'M/d', 'America/New_York')`✏️	`10/14`
`format_date('1983-10-14T13:07:04.054Z', 'h:m', 'America/New_York')`✏️	`9:7`

geo_distance

Calculates the crow-fly distance between two lat/lon. Note that is based on the road conditions or real-world infrastructure.

get_value

Returns the value from current context. Depending on where/when formula is being executed, the list of available fields varies. Consult product docs for more info on available fields.

If a field is not available, then returns null

group

group(input: T[], keyProvider: string | (it: T) => string)

Groups given input array by specified keyProvider and returns array containing two values key and items.

If keyProvider is string, then that field of given object will be used as Key
If keyProvider is function, then that function will be called for each input element and string returned from function will be used as Key
Keys are grouped case-insensitively. the returned key will be first key found if there are multiple keys with different cases.

Expression	Result
`group([{state: 'KA', city: 'Bangalore'}, {state: 'KA', city: 'Davangere'}, {state: 'AP', city: 'Vizaq'}], (it) => it.state)`✏️	`[{"key":"AP","items":[{"state":"AP","city":"Vizaq"}]},{"key":"KA","items":[{"state":"KA","city":"Bangalore"},{"state":"KA","city":"Davangere"}]}]`
`group(['KA', 'KA', 'AP'], (it) => it)`✏️	`[{"key":"AP","items":["AP"]},{"key":"KA","items":["KA","KA"]}]`

parse_number

parse_number(input: string, format?: 'us' | 'eu'): number

Prases given string and tries to extract number according to given format. If format is not given then tries to understand the format automatically depending on number of comma/decimal chars in the input. If there is single comma, then format is assumed as us

Expression	Result
`parse_number('')`✏️	`<null>`
`parse_number('1.0')`✏️	`1`
`parse_number('1,05')`✏️	`105`
`parse_number('1,495')`✏️	`1495`
`parse_number('USD1,495')`✏️	`1495`
`parse_number('-USD1,495')`✏️	`-1495`
`parse_number('(USD1,495')`✏️	`1495`
`parse_number('usd -1,495')`✏️	`-1495`
`parse_number('(usd 1,495')`✏️	`1495`
`parse_number('$1,495')`✏️	`1495`
`parse_number('-$1,495')`✏️	`-1495`
`parse_number('($1,495')`✏️	`1495`
`parse_number('1,05.01')`✏️	`105.01`
`parse_number('-1,05.01')`✏️	`-105.01`
`parse_number('(1,05.01')`✏️	`105.01`
`parse_number('1,001,05.01')`✏️	`100105.01`
`parse_number('-1,001,05.01')`✏️	`-100105.01`
`parse_number('(1,001,05.01')`✏️	`100105.01`
`parse_number('1.05,01')`✏️	`105.01`
`parse_number('1.001.05,01')`✏️	`100105.01`
`parse_number('-1.001.05,01')`✏️	`-100105.01`
`parse_number('(1.001.05,01')`✏️	`100105.01`
`parse_number('1.05,01')`✏️	`105.01`
`parse_number('1,05,01')`✏️	`10501`
`parse_number('1.05.01')`✏️	`10501`
`parse_number('-1.05.01')`✏️	`-10501`
`parse_number('(1.05.01')`✏️	`10501`

format_number

format_number(input: number, format?: 'us' | 'eu' | string): string

Formats input number as per format string

If input is blank, then returns it as is
If format is blank, then assumes it is us
It supports formatting numbers, bytes, percentages and currency. See below examples for various use cases

Numbers

Expression	Result
`format_number(10000, '0,0.0000')`✏️	`10,000.0000`
`format_number(10000.23, '0,0')`✏️	`10,000`
`format_number(10000.23, '+0,0')`✏️	`+10,000`
`format_number(-10000, '0,0.0')`✏️	`-10,000.0`
`format_number(10000.1234, '0.000')`✏️	`10000.123`
`format_number(100.1234, '00000')`✏️	`00100`
`format_number(1000.1234, '000000,0')`✏️	`001,000`
`format_number(10, '000.00')`✏️	`010.00`
`format_number(10000.1234, '0[.]00000')`✏️	`10000.12340`
`format_number(-10000, '(0,0.0000)')`✏️	`(10,000.0000)`
`format_number(-0.23, '.00')`✏️	`-.23`
`format_number(-0.23, '(.00)')`✏️	`(.23)`
`format_number(0.23, '0.00000')`✏️	`0.23000`
`format_number(0.23, '0.0[0000]')`✏️	`0.23`
`format_number(1230974, '0.0a')`✏️	`1.2m`
`format_number(1460, '0 a')`✏️	`1 k`
`format_number(-104000, '0a')`✏️	`-104k`
`format_number(1, '0o')`✏️	`1st`
`format_number(100, '0o')`✏️	`100th`

Currency

Expression	Result
`format_number(1000.234, '$0,0.00')`✏️	`$1,000.23`
`format_number(1000.2, '0,0[.]00 $')`✏️	`1,000.20 $`
`format_number(1001, '$ 0,0[.]00')`✏️	`$ 1,001`
`format_number(-1000.234, '($0,0)')`✏️	`($1,000)`
`format_number(-1000.234, '$0.00')`✏️	`-$1000.23`
`format_number(1230974, '($ 0.00 a)')`✏️	`$ 1.23 m`

Bytes

Expression	Result
`format_number(100, '0b')`✏️	`100B`
`format_number(1024, '0b')`✏️	`1KB`
`format_number(2048, '0 ib')`✏️	`2 KiB`
`format_number(3072, '0.0 b')`✏️	`3.1 KB`
`format_number(7884486213, '0.00b')`✏️	`7.88GB`
`format_number(3467479682787, '0.000 ib')`✏️	`3.154 TiB`

Percentages

Expression	Result
`format_number(1, '0%')`✏️	`100%`
`format_number(0.974878234, '0.000%')`✏️	`97.488%`
`format_number(-0.43, '0%')`✏️	`-43%`
`format_number(0.43, '(0.000%)')`✏️	`43.000%`

is_array

is_array(input)

Checks if given input is array and returns true, otherwise returns false.

Expression	Result
`is_array(['abc','Anu'])`✏️	`true`
`is_array(['',''])`✏️	`true`
`is_array('John')`✏️	`false`
`is_array([3,4])`✏️	`true`

is_blank

Checks if value is blank (null or string with spaces)

Expression	Result
`is_blank(['abc','Anu'])`✏️	`false`
`is_blank(['',''])`✏️	`false`
`is_blank('John')`✏️	`false`
`is_blank()`✏️	`true`
`is_blank('')`✏️	`true`

is_equal

Checks if two values have same content

Expression	Result
`is_equal(['abc','Anu'])`✏️	`false`
`is_equal('','')`✏️	`true`
`is_equal('abc','abc')`✏️	`true`
`is_equal(23,23)`✏️	`true`
`is_equal('')`✏️	`false`

is_equal_any

is_equal_any(input, values): boolean

Checks if input is equal to any one of the value in the values array (case-insensitive) and returns true, otherwise returns false.

If input is null, return false
If values is null or blank, then returns false

Expression	Result
`is_equal_any('abc',['abc','zya'])`✏️	`true`
`is_equal_any('abc',['xyz','lmk'])`✏️	`false`

is_nan

Checks if given input is NaN

Expression	Result
`is_nan(123)`✏️	`false`
`is_NaN(-1.23)`✏️	`false`
`is_NaN(5-2)`✏️	`false`

is_not_blank

Negation of is_blank

Expression	Result
`is_not_blank(7)`✏️	`true`
`is_not_blank()`✏️	`false`
`is_not_blank('')`✏️	`false`
`is_not_blank('John')`✏️	`true`

is_not_null

Negation of is_null

Expression	Result
`is_not_null('7')`✏️	`true`
`is_not_null('abc')`✏️	`true`
`is_not_null()`✏️	`false`
`is_not_null('')`✏️	`true`

is_number

is_number(input)

Checks if given input is number and returns true, otherwise returns false. Note that string which contains valid number still returns false. If you want to check if string can be converted to a number, then use is_number_like.

Expression	Result
`is_number(1234)`✏️	`true`
`is_number('1234')`✏️	`false`
`is_number(0)`✏️	`true`
`is_number('abc')`✏️	`false`

is_number_like

is_number_like(input)

Checks if given input is number or string which can be converted to a valid number and returns true, otherwise returns false. Note that string which contains valid number still returns false.

Expression	Result
`is_number_like('abc')`✏️	`false`
`is_number_like('123')`✏️	`true`

is_zero

is_zero(input)

Checks if given input equal to 0 and returns true, otherwise returns false.

Expression	Result
`is_zero(' 0')`✏️	`true`
`is_zero('10')`✏️	`false`

parse_json

parse_json(val)

Parses a JSON string into an object. If val is invalid, it handles errors by returning a default value or throwing an error with a detailed message.

The function first checks if the input val is blank or NaN. If true, it returns an empty object .
If val is not a string, it returns val unchanged.
If parsing fails (i.e., the string is not a valid JSON), it throws an error with a message that includes the error and the original JSON string.

Expression	Result
`parse_json('{ "name":"John", "age":30, "city":"New York"}')`✏️	`{"name":"John","age":30,"city":"New York"}`
`parse_json('')`✏️	`{}`
`parse_json('{ invalid json ')`✏️	`Error: Error parsing the json string. Error [Expected property name or '}' in JSON at position 2]. Json [{ invalid json ]`
`parse_json('')`✏️	`{}`
`parse_json(null)`✏️	`{}`

to_json

Converts input into json string

to_json_pretty

Converts input into pretty formatted json string

length

length(input)

Returns the length of input.

If input is null, then returns 0.
If input is array, then returns array length.
If input is string, then returns string length.

lower_case

lower_case(value: any | any[]): any | any[]

Converts given text to lower-cased text.

If input is an Array, then it operates on each element of Array.

Expression	Result
`lower_case('JOHN DOE')`✏️	`john doe`

map_value

map_value(input, mappings, [defaultValue])

Checks the input against values in mappings array and returns first match. If there are no matches found, then returns defaultValue or null if default value is not specified. This is similar to switch.

Mappings should be an array with even number of values with series of key=value pairs
If input is blank, returns defaultValue
If mappings is blank, returns defaultValue
input is matching is done case-insensitively
mappings value could be coming from params. For ex., $params.stateMappings

Expression	Result
`map_value('CA', ['ca', 'California', 'ny', 'New York'])`✏️	`California`
`map_value('Ny', ['ca', 'California', 'ny', 'New York'])`✏️	`New York`
`map_value('NV', ['ca', 'California', 'ny', 'New York'])`✏️	`<null>`
`map_value('NV', ['ca', 'California', 'ny', 'New York'], 'No State')`✏️	`No State`

map_value_reverse

map_value_reverse(input, mappings, [defaultValue])

Same as map_value but uses the given mapset in reverse order (that is matches input with value of key=value pairs and returns the key)

If there are multiple matches, picks the first match
Look for notes section of map_value for additional details

Expression	Result
`map_value_reverse('california', ['ca', 'California', 'ny', 'New York'])`✏️	`ca`
`map_value_reverse('california', ['CA', 'California', 'ny', 'New York'])`✏️	`CA`
`map_value_reverse('california', ['CA', 'California', 'cali', 'california', 'ny', 'New York'])`✏️	`CA`
`map_value('Ny', ['ca', 'California', 'ny', 'New York'])`✏️	`New York`
`map_value('NV', ['ca', 'California', 'ny', 'New York'], 'No State')`✏️	`No State`

map_bucket

map_bucket(value, buckets, defaultVal?)

Maps the value to a bucket based on range definitions. Each bucket is defined as a 3-element array: [min, max, bucketValue].

If the value falls within a bucket's range (inclusive), returns that bucket's value
If min is null, treats it as negative infinity
If max is null, treats it as positive infinity
If no bucket matches, returns the defaultVal
Supports both numeric and string ranges
Processes buckets in order, returns first matching bucket
If a bucket item has less than 3 elements, then that bucket is ignored. If it has more than 3 elements, then first 3 elements are considered.

Expression	Result
`map_bucket(5, [[0,10,'low'],[11,20,'medium'],[21,30,'high']])`✏️	`low`
`map_bucket(10, [[0,10,'low'],[11,20,'medium']])`✏️	`low`
`map_bucket(50, [[0,10,'low'],[11,20,'medium'],[21,null,'high']])`✏️	`high`
`map_bucket('C', [['A','F','first'],['G','M','second'],['N','Z','third']])`✏️	`first`
`map_bucket('K', [['A','F','first'],['G','M','second'],['N','Z','third']])`✏️	`second`
`map_bucket(5, [[null,10,'below_ten'],[10,null,'ten_plus']])`✏️	`below_ten`
`map_bucket(15, [[0,10,'low']], 'out_of_range')`✏️	`out_of_range`
`map_bucket('C', [['A','F','letters'],[0,9,'numbers'],[null,null,'other']])`✏️	`letters`
`map_bucket(5, [['A','F','letters'],[0,9,'numbers'],[null,null,'other']])`✏️	`numbers`

max

Returns the max value among given series of values

min

Returns the min value among given series of values

month_names

Returns array consisting of month names.

['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']

month_names_short

Returns array consisting of month first 3 chars of names

['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']

mpicklist_map_value

mpicklist_map_value(input: string, mapset: string[], defaultValue?: string)

Maps the individual multi-picklist value inside input using mapset array with defaultValue as default

mpicklist_cleanup

mpicklist_cleanup(input: string)

Cleanups the given multi-picklist input

Trims the individual Picklist values
Removes the duplicate values
Removes blank values

Expression	Result
`mpicklist_cleanup('abc;abc')`✏️	`abc`
`mpicklist_cleanup('xyz;abc ;xyz; abc;abc')`✏️	`xyz;abc`
`mpicklist_cleanup('abc;abc;;hello;;')`✏️	`abc;hello`

mpicklist_sort

mpicklist_sort(input: string)

Sorts the given multi-picklist input

It doesn't alter the individual values. If you wan to remove blanks or trim values, use mpicklist_cleanup

Expression	Result
`mpicklist_sort('CA;NY;MD; OR;')`✏️	`; OR;CA;MD;NY`

mpicklist_add_value

mpicklist_add_value(input: string)

Appends given value to input multi picklist.

Value could be single value or an Array
Doesn't add the value if value already exists

Expression	Result
`mpicklist_add_value('CA', 'NY')`✏️	`CA;NY`
`mpicklist_add_value('CA', ['NY', 'WA'])`✏️	`CA;NY;WA`
`mpicklist_add_value('CA', ['CA'])`✏️	`CA`

mpicklist_remove_value

mpicklist_remove_value(input: string, removeValue: string)

Removes given value from input multi picklist, if it exists

Value could be a single value or an Array
Returns null if all values are removed from the input

Expression	Result
`mpicklist_remove_value('CA;ny', 'NY')`✏️	`CA`
`mpicklist_remove_value('CA;ny;som;Wa', ['NY', 'WA'])`✏️	`CA;som`
`mpicklist_remove_value('CA', ['CA'])`✏️	`<null>`

new_array

new_array(length: number, fillValueOrFunction: any | (indexValue, index, array) => any)

Creates new array of length and fills the array using fillValueOrFunction. See fill_array for more information on fill function.

If length is not a number or less than 0 then defaults to 0
Maximum length array you can create is 10,000. If length is bigger than that, then it will error out.
If fillValueOrFunction is not specified then it defaults to null

Expression	Result
`new_array(1)`✏️	`[null]`
`new_array(5, 10)`✏️	`[10,10,10,10,10]`
`new_array(2, 'abc')`✏️	`["abc","abc"]`
`new_array(5, (value, index) => index + 1)`✏️	`[1,2,3,4,5]`
`new_array()`✏️	`[]`
`new_array(-1)`✏️	`[]`

now

now()

Returns current time in ISO 8601 extended format. For ex., 2020-01-01T18:23:45Z.

now_iso_basic

now_iso_basic()

Returns current time in ISO 8601 basic format. For ex., 20200101T182345.

null_if_blank

null_if_blank(input)

Returns blank string if input is null, otherwise returns the input as is.

Expression	Result
`null_if_blank('')`✏️	`<null>`
`null_if_blank(' ')`✏️	`<null>`
`null_if_blank(' Jack ')`✏️	`Jack`
`null_if_blank('John')`✏️	`John`
`null_if_blank(10)`✏️	`10`
`null_if_blank([1, 2])`✏️	`[1,2]`

null_if_zero

If input is null, then returns 0

Expression	Result
`null_if_zero(0)`✏️	`<null>`
`null_if_zero(10)`✏️	`10`
`null_if_zero('abc')`✏️	`abc`

pad

Pads both right and left side of the value upto size using chars

pad_end

Pads the right side of value upto size using chars

pad_start

Pads the start side of value upto size using chars

parse_date

parse_date(input, pattern, timeZone?)

Parses input date string according to pattern in zone. This function uses Luxon library to parse string. You can refer to that documentation also to get additional context.

If input is blank, then defaults to
If pattern is blank, then it returns iso datetime string
If timeZone is blank, then defaults to UTC timeZone.
List of valid Timezone Ids are documented here

Pre-defined Formats

Pattern	Description
`iso`	Parses string as iso date/datetime. It parses all datetime string listed here.
`ms`	Assumes the input as number of milliseconds since unix epoch
`secs`	Assumes the input as number of seconds since unix epoch

Parse Tokens

Token	Description	Example
`S`	millisecond, no padding	`54`
`SSS`	millisecond, padded to 3	`054`
`u`	fractional seconds, (5 is a half second, 54 is slightly more)	`54`
`uu`	fractional seconds, (one or two digits)	`05`
`uuu`	fractional seconds, (only one digit)	`5`
`s`	second, no padding	`4`
`ss`	second, padded to 2 padding	`04`
`m`	minute, no padding	`7`
`mm`	minute, padded to 2	`07`
`h`	hour in 12-hour time, no padding	`1`
`hh`	hour in 12-hour time, padded to 2	`01`
`H`	hour in 24-hour time, no padding	`13`
`HH`	hour in 24-hour time, padded to 2	`13`
`Z`	narrow offset	`+5`
`ZZ`	short offset	`+05:00`
`ZZZ`	techie offset	`+0500`
`z`	IANA zone	`America/New_York`
`a`	meridiem	`AM`
`d`	day of the month, no padding	`6`
`dd`	day of the month, padded to 2	`06`
`E`	day of the week, as number from 1-7 (Monday is 1, Sunday is 7)	`3`
`EEE`	day of the week, as an abbreviate localized string	`Wed`
`EEEE`	day of the week, as an unabbreviated localized string	`Wednesday`
`M`	month as an unpadded number	`8`
`MM`	month as an padded number	`08`
`MMM`	month as an abbreviated localized string	`Aug`
`MMMM`	month as an unabbreviated localized string	`August`
`y`	year, 1-6 digits, very literally	`2014`
`yy`	two-digit year, interpreted as > 1960 (also accepts 4)	`14`
`yyyy`	four-digit year	`2014`
`yyyyy`	four- to six-digit years	`10340`
`yyyyyy`	six-digit years	`010340`
`G`	abbreviated localized era	`AD`
`GG`	unabbreviated localized era	`Anno Domini`
`GGGGG`	one-letter localized era	`A`
`kk`	ISO week year, unpadded	`17`
`kkkk`	ISO week year, padded to 4	`2014`
`W`	ISO week number, unpadded	`32`
`WW`	ISO week number, padded to 2	`32`
`o`	ordinal (day of year), unpadded	`218`
`ooo`	ordinal (day of year), padded to 3	`218`
`q`	quarter, no padding	`3`
`D`	localized numeric date	`9/6/2014`
`DD`	localized date with abbreviated month	`Aug 6, 2014`
`DDD`	localized date with full month	`August 6, 2014`
`DDDD`	localized date with full month and weekday	`Wednesday, August 6, 2014`
`t`	localized time	`1:07 AM`
`tt`	localized time with seconds	`1:07:04 PM`
`T`	localized 24-hour time	`13:07`
`TT`	localized 24-hour time with seconds	`13:07:04`
`TTT`	localized 24-hour time with seconds and abbreviated offset	`13:07:04 EDT`
`f`	short localized date and time	`8/6/2014, 1:07 PM`
`ff`	less short localized date and time	`Aug 6, 2014, 1:07 PM`
`F`	short localized date and time with seconds	`8/6/2014, 1:07:04 PM`
`FF`	less short localized date and time with seconds	`Aug 6, 2014, 1:07:04 PM`

parse_url

parse_url(url)

Parses a URL string and returns an object containing its components.

Returns an object with the following properties:

url: Full URL string
protocol: Protocol/scheme (e.g., 'http', 'https')
host: Full host including port if specified
hostName: Host name without port
port: Port number if specified
path: URL path
hash: URL fragment/hash
query: Query parameters object
search: Query string
user: User part of credentials
password: Password part of credentials

Expression	Result
`parse_url('https://user:pass@example.com:8080/path?q=123#section')`✏️	`{"protocol":"https","port":"8080","host":"example.com:8080","user":"user","password":"pass","hash":"section","search":"q=123","query":{"q":"123"},"url":"https://user:pass@example.com:8080/path?q=123#section","path":"/path","hostName":"example.com"}`
`parse_url('http://localhost:3000/api/data')`✏️	`{"protocol":"http","port":"3000","host":"localhost:3000","user":"","password":"","hash":"","search":"","query":{},"url":"http://localhost:3000/api/data","path":"/api/data","hostName":"localhost"}`
`parse_url('sftp://storage.company.com/files/doc.pdf')`✏️	`{"protocol":"sftp","port":"","host":"storage.company.com","user":"","password":"","hash":"","search":"","query":{},"url":"sftp://storage.company.com/files/doc.pdf","path":"/files/doc.pdf","hostName":"storage.company.com"}`
`parse_url('https://test.com/files?key1=value1&key1=value2')`✏️	`{"protocol":"https","port":"","host":"test.com","user":"","password":"","hash":"","search":"key1=value1&key1=value2","query":{"key1":"value2"},"url":"https://test.com/files?key1=value1&key1=value2","path":"/files","hostName":"test.com"}`

convert_date

convert_date(input, parsePattern, formatPattern, timeZone?)

Parses input date string according to parsePattern in timeZone and then formats that date into format given in formatPattern. This basically combines parse_date with format_date as this convention is used many times.

Old name for this function was parse_date_to_format

If input is blank, then returns input as is
If parsePattern is blank, then it returns input as is
If formatPattern is blank, then it formats it in iso datetime format
If timeZone is blank, then defaults to UTC timeZone
The list of valid Timezone Ids are documented here

random_boolean

Returns random boolean (true or false)

random_integer

Returns random integer between two values.

Expression	Result
`random_integer(1,3)`✏️	`3`
`random_integer(2,100)`✏️	`49`

random_null

Randomly returns null and other times it returns one the given values

Expression	Result
`random_null(3)`✏️	`<null>`
`random_null(3)`✏️	`3`
`random_null(3)`✏️	`3`
`random_null(3)`✏️	`<null>`
`random_null(3)`✏️	`3`
`random_null(3)`✏️	`3`
`random_null(3)`✏️	`<null>`
`random_null(3)`✏️	`<null>`

random_string

Returns random string of length (default 100). for ex,. lsmztmc4ali1nnrk6972gdjwu64uk6xfkimwdpc1sgbk9gg6dkickh718c2kvovz13wnxlndgc3h97g1kuv7e9wbmwtpnalqx2ut

random_value

random_value(values)

Returns one of the given values at random

Expression	Result
`random_value(3,34,5,88,99,00)`✏️	`3`
`random_value()`✏️	`<null>`

remove_prefix

Removes the value if it is starts with given string

Expression	Result
`remove_prefix('S-Kumar','S-')`✏️	`Kumar`
`remove_prefix('abc','abc')`✏️	`<blank>`
`remove_prefix('S-Kumar','T-')`✏️	`S-Kumar`

remove_suffix

remove_suffix(input: any, suffix: string | string[]): string

Removes the suffix from string, if it is present. If not, returns the input as is. Suffix can be multiple values and in that case it removes any one of those suffixes from input.

::: warning Caveat If you specify multiple suffixes, once a suffix is matched, then input is returned. For ex., remove_suffix('Boing-K-J', ['-K', '-J']) => Boing-K :::

Expression	Result
`remove_suffix('AFAWGAGT25A-J', '-J')`✏️	`AFAWGAGT25A`
`remove_suffix('AFAWGAGT25A-K', '-J')`✏️	`AFAWGAGT25A-K`
`remove_suffix('AFAWGAGT25A-K', ['-J', '-K'])`✏️	`AFAWGAGT25A`

repeat

Repeats given input times using separator (if specified, else blank).

Expression	Result
`repeat(3,4,'-')`✏️	`3-3-3-3`
`repeat(3,0,'-')`✏️	`<blank>`
`repeat(3,1,'-')`✏️	`3`

replace_regex

replace_regex(input, regex, replaceWith?)

Replace all matches in input for regex with replaceWith string. If replaceWith is not provided, then uses blank.

replace_whitespace

replace_whitespace(input, replaceWith?)

Replace all whitespaces in input with replaceWith string. If replaceWith is not provided, then uses blank.

round

round(input, precision)

Rounds the value using Half Up method to specified precision

If precision is not specified, then it is assumed to be 0
Supports -ve precision. See below, for examples.
If non-number input is passed, then input is returned as is.

Expression	Result
`round(10)`✏️	`10`
`round(10.4)`✏️	`10`
`round(10.5)`✏️	`11`
`round(10.9)`✏️	`11`
`round(0.4)`✏️	`0`
`round(0.5)`✏️	`1`
`round(0.6)`✏️	`1`
`round(847392615.536178294, -10)`✏️	`0`
`round(847392615.536178294, -9)`✏️	`1000000000`
`round(847392615.536178294, -8)`✏️	`800000000`
`round(847392615.536178294, -7)`✏️	`850000000`
`round(847392615.536178294, -6)`✏️	`847000000`
`round(847392615.536178294, -5)`✏️	`847400000`
`round(847392615.536178294, -4)`✏️	`847390000`
`round(847392615.536178294, -3)`✏️	`847393000`
`round(847392615.536178294, -2)`✏️	`847392600`
`round(847392615.536178294, -1)`✏️	`847392620`
`round(847392615.536178294, 0)`✏️	`847392616`
`round(847392615.536178294, 1)`✏️	`847392615.5`
`round(847392615.536178294, 2)`✏️	`847392615.54`
`round(847392615.536178294, 3)`✏️	`847392615.536`
`round(847392615.536178294, 4)`✏️	`847392615.5362`
`round(847392615.536178294, 5)`✏️	`847392615.53618`
`round(847392615.536178294, 6)`✏️	`847392615.536178`
`round(847392615.536178294, 7)`✏️	`847392615.5361784`
`round(847392615.536178294, 8)`✏️	`847392615.5361784`
`round(847392615.536178294, 9)`✏️	`847392615.5361784`
`round({foo: 'bar'})`✏️	`{"foo":"bar"}`
`round('bar')`✏️	`bar`
`round(null)`✏️	`<null>`
`round(undefined)`✏️	`<null>`

round_down

round_down(input, precision)

Rounds down the given input number to specified precision

If precision is not specified, then it is assumed to be 0
Supports -ve precision. See below, for examples.
If non-number input is passed, then input is returned as is.

Expression	Result
`round_down(10)`✏️	`10`
`round_down(10.4)`✏️	`10`
`round_down(10.5)`✏️	`10`
`round_down(10.9)`✏️	`10`
`round_down(0.4)`✏️	`0`
`round_down(0.5)`✏️	`0`
`round_down(0.6)`✏️	`0`
`round_down(847392615.536178294, -10)`✏️	`0`
`round_down(847392615.536178294, -9)`✏️	`0`
`round_down(847392615.536178294, -8)`✏️	`800000000`
`round_down(847392615.536178294, -7)`✏️	`840000000`
`round_down(847392615.536178294, -6)`✏️	`847000000`
`round_down(847392615.536178294, -5)`✏️	`847300000`
`round_down(847392615.536178294, -4)`✏️	`847390000`
`round_down(847392615.536178294, -3)`✏️	`847392000`
`round_down(847392615.536178294, -2)`✏️	`847392600`
`round_down(847392615.536178294, -1)`✏️	`847392610`
`round_down(847392615.536178294, 0)`✏️	`847392615`
`round_down(847392615.536178294, 1)`✏️	`847392615.5`
`round_down(847392615.536178294, 2)`✏️	`847392615.53`
`round_down(847392615.536178294, 3)`✏️	`847392615.536`
`round_down(847392615.536178294, 4)`✏️	`847392615.5361`
`round_down(847392615.536178294, 5)`✏️	`847392615.53617`
`round_down(847392615.536178294, 6)`✏️	`847392615.536178`
`round_down(847392615.536178294, 7)`✏️	`847392615.5361784`
`round_down(847392615.536178294, 8)`✏️	`847392615.5361784`
`round_down(847392615.536178294, 9)`✏️	`847392615.5361784`
`round_down({foo: 'bar'})`✏️	`{"foo":"bar"}`
`round_down('bar')`✏️	`bar`
`round_down(null)`✏️	`<null>`
`round_down(undefined)`✏️	`<null>`

round_up

round_up(input, precision)

Rounds up the given input to specified precision

If precision is not specified, then it is assumed to be 0
Supports -ve precision. See below, for examples.
If non-number input is passed, then input is returned as is.

Expression	Result
`round_up(10)`✏️	`10`
`round_up(10.4)`✏️	`11`
`round_up(10.5)`✏️	`11`
`round_up(10.9)`✏️	`11`
`round_up(0.4)`✏️	`1`
`round_up(0.5)`✏️	`1`
`round_up(0.6)`✏️	`1`
`round_up(847392615.536178294, -9)`✏️	`1000000000`
`round_up(847392615.536178294, -8)`✏️	`900000000`
`round_up(847392615.536178294, -7)`✏️	`850000000`
`round_up(847392615.536178294, -6)`✏️	`848000000`
`round_up(847392615.536178294, -5)`✏️	`847400000`
`round_up(847392615.536178294, -4)`✏️	`847400000`
`round_up(847392615.536178294, -3)`✏️	`847393000`
`round_up(847392615.536178294, -2)`✏️	`847392700`
`round_up(847392615.536178294, -1)`✏️	`847392620`
`round_up(847392615.536178294, 0)`✏️	`847392616`
`round_up(847392615.536178294, 1)`✏️	`847392615.6`
`round_up(847392615.536178294, 2)`✏️	`847392615.54`
`round_up(847392615.536178294, 3)`✏️	`847392615.537`
`round_up(847392615.536178294, 4)`✏️	`847392615.5362`
`round_up(847392615.536178294, 5)`✏️	`847392615.53618`
`round_up(847392615.536178294, 6)`✏️	`847392615.536179`
`round_up(847392615.536178294, 7)`✏️	`847392615.5361784`
`round_up(847392615.536178294, 8)`✏️	`847392615.5361784`
`round_up(847392615.536178294, 9)`✏️	`847392615.5361784`
`round_up(847392615.536178294, 10)`✏️	`847392615.5361784`
`round_down({foo: 'bar'})`✏️	`{"foo":"bar"}`
`round_down('bar')`✏️	`bar`
`round_down(null)`✏️	`<null>`
`round_down(undefined)`✏️	`<null>`

sequence

Generates length (defaults to 0) array of sequence numbers with start (default 0) and sequence step of step (default 1). Step can be -ve and in that case, numbers will be decremented

sf_id15

Converts salesforce ids into 15 digit one. If input is null or not equal to 18 chars, then returns the input as is.

sf_id18

Converts salesforce ids into 18 digit one. If input is null or not equal to 15 chars, then returns the input as is.

soql_in_string

Creates soql in clause compatible string (comma separated string with ' escaped). Second arg can be used to default if first argument is null or array containing zero items.

soql_string

Converts given input to string with ' escaped and wrapped with 's. If input is null, then returns ''

split

split(input: string, separator: string, options?: SplitOptions)

Splits input string by separator and returns the string array.

If input is null then returns empty array
If separator is blank then returns array containing input as is
Separator is treated as case-insensitive
Depending on the separator in the input, blanks or values with spaces may be returned in the output. If you want to remove blanks, specify remove_blanks and or trim as options

Where SplitOptions is:

Option	Description
`remove_blanks`	Removes blanks from resulting array
`trim`	Trims the values of resulting array

Expression	Result
`split('This is my home', ' ')`✏️	`["This","is","my","home"]`
`split('This is my Home and this is my Car', 'IS MY')`✏️	`["This "," Home and this "," Car"]`
`split('This is my Home and this is my Car', 'IS MY', {trim: true})`✏️	`["This","Home and this","Car"]`
`split('This is my Home and this is my Caris my', 'IS MY', {trim: true})`✏️	`["This","Home and this","Car",""]`
`split('This is my Home and this is myis my Car', 'IS MY', {trim: true})`✏️	`["This","Home and this","","Car"]`
`split('This is my Home and this is myis my Car', 'IS MY', {trim: true, remove_blanks: true})`✏️	`["This","Home and this","Car"]`

starts_with

starts_with(input: string | number, prefix: string): boolean

Checks if input starts with prefix (case-insensitive) and returns true, otherwise returns false

If input or prefix is blank then returns false
If input is number then it is converted to string before checking prefix

starts_with_any

starts_with_any(input, ...prefixes): boolean

Checks if input starts with any one of the prefixes (case-insensitive) and returns true, otherwise returns false

If input is blank or prefix is blank then returns false
If input is number, then it is converted to string before checking prefix

to_array

Converts given value to array. if value is already an array, then returns it as is.

to_boolean

Converts value to boolean. If value is false, 0, or null then treated as false and everything else as true

Expression	Result
`to_boolean('false')`✏️	`false`
`to_boolean('FALSE')`✏️	`false`
`to_boolean('0')`✏️	`false`
`to_boolean('no')`✏️	`false`
`to_boolean('NO')`✏️	`false`
`to_boolean(false)`✏️	`false`
`to_boolean('true')`✏️	`true`
`to_boolean('TRUE')`✏️	`true`
`to_boolean('1')`✏️	`true`
`to_boolean('yes')`✏️	`true`
`to_boolean('anything')`✏️	`true`
`to_boolean(null)`✏️	`false`
`to_boolean(undefined)`✏️	`false`
`to_boolean(NaN)`✏️	`false`
`to_boolean(1)`✏️	`true`
`to_boolean(0)`✏️	`false`
`to_boolean(true)`✏️	`true`
`to_boolean({})`✏️	`true`
`to_boolean([])`✏️	`true`

to_integer

to_integer(input, defaultValue?): number

Converts input to integer. If it cannot be converted, then throws error unless a default is provided.

Expression	Result
`to_integer('10')`✏️	`10`
`to_integer('abc')`✏️	`Error: Invalid integer value abc`
`to_integer('abc', 20)`✏️	`20`

to_number

to_number(input, default?): number

Converts given input to number. If it cannot be converted, then throws error unless a default is provided.

to_string

Converts given value to string in human-readable format. If value is null, then returns ''

Expression	Result
`to_string(33)`✏️	`33`
`to_string('Jake')`✏️	`Jake`
`to_string(['Jake', 'John', 10, 100.5])`✏️	`[Jake, John, 10, 100.5]`
`to_string({first: 'Jake', last: 'John'})`✏️	`{first=Jake, last=John}`
`to_string([{first: 'Jake', last: 'John'}, {first: 'Jane', last: 'Doe'}])`✏️	`[{first=Jake, last=John}, {first=Jane, last=Doe}]`
`to_string(null)`✏️	`<blank>`
`to_string(undefined)`✏️	`<blank>`
`to_string(NaN)`✏️	`<blank>`

today

today()

Returns today's date in yyyy-MM-dd format. For ex., 2020-05-23

Expression	Result
`today()`✏️	`2025-01-30`

trim

trim(input: string | string[]): string | string[]

Removes any white spaces from the beginning and end of input.

Expression	Result
`trim('Boing ')`✏️	`Boing`
`trim(' Datasert')`✏️	`Datasert`
`trim(' Salesforce ')`✏️	`Salesforce`

trim_end

trim_end(value: any | any[]): any | any[]

Removes any white spaces from end of text. It does not check or alter beginning of strings.

Expression	Result
`trim_end('Boing ')`✏️	`Boing`
`trim_end(' Datasert')`✏️	`⠀Datasert`
`trim_end(' Salesforce ')`✏️	`⠀⠀Salesforce`

trim_start

trim_start(value: any | any[]): any | any[]

Removes any whitespaces from the beginning of text. It does not check or alter end of strings.

Expression	Result
`trim_start(' Boing')`✏️	`Boing`
`trim_start('Datasert ')`✏️	`Datasert⠀`
`trim_start(' Salesforce ')`✏️	`Salesforce⠀`

truncate_end

truncate_end(input: string, length: number)

Same as truncate_start but truncates end of the value instead of beginning.

Expression	Result
`truncate_end('This is my Home', 10)`✏️	`This is my`
`truncate_end('This is my Home', null)`✏️	`This is my Home`
`truncate_end('David', 10)`✏️	`David`
`truncate_end(null, 10)`✏️	`<null>`
`truncate_end(null, 10)`✏️	`<null>`

truncate_start

truncate_start(input: string, length: number)

Truncates the beginning of the given input to length chars.

If input or length is null then input is returned as is
If input size is less or equal to length, then input is returned as is
If length is less than or equal to 0, then input is returned as is

Expression	Result
`truncate_start('This is my Home', 10)`✏️	`is my Home`
`truncate_start('This is my Home', null)`✏️	`This is my Home`
`truncate_start('David', 10)`✏️	`David`
`truncate_start(null, 10)`✏️	`<null>`
`truncate_start(null, 10)`✏️	`<null>`

unix_time

unix_time()

Returns number of seconds since Unix Epoch time (1970 Jan 1st). For ex., 1614829329

Expression	Result
`unix_time()`✏️	`1738257514`

unix_time_ms

unix_time_ms()

Returns number of milliseconds since Unix Epoch time (1970 Jan 1st). For ex., 1614829329000

Expression	Result
`unix_time_ms()`✏️	`1738257514288`

unwrap

Unwraps the value if it is wrapped with given string. If it is not wrapped with given string, then returns value as is.

Expression	Result
`unwrap('car-BMW-car','car')`✏️	`-BMW-`
`unwrap('car-BMW-car','bus')`✏️	`car-BMW-car`
`unwrap('car-BMW-car','')`✏️	`car-BMW-car`

upper_case

upper_case(value: any | any[]): any | any[]

Converts given text to upper-cased text.

If input is an Array, then it operates on each element of Array.

Expression	Result
`upper_case('John Doe')`✏️	`JOHN DOE`
`upper_case(['Prakash','Santhosh'])`✏️	`PRAKASH,SANTHOSH`

url_decode

Url decodes the given input. If input is null then returns input as is.

Expression	Result
`url_decode('my%20test.asp?name=st%C3%A5le&car=saab')`✏️	`my test.asp?name=ståle&car=saab`
`url_decode(null)`✏️	`<null>`

url_encode

Url encodes the given input. If input is null then returns input as is.

Expression	Result
`url_encode('my test.asp?name=ståle&car=saab')`✏️	`my%20test.asp?name=st%C3%A5le&car=saab`
`url_encode(null)`✏️	`<null>`

us_state_codes

Returns array consisting of US state codes

['AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY']

us_state_names

Returns array consisting of US state names

['Alabama', 'Alaska', 'Arizona', 'Arkansas', 'California', 'Colorado', 'Connecticut', 'Delaware', 'District of Columbia', 'Florida', 'Georgia', 'Hawaii', 'Idaho', 'Illinois', 'Indiana', 'Iowa', 'Kansas', 'Kentucky', 'Louisiana', 'Maine', 'Maryland', 'Massachusetts', 'Michigan', 'Minnesota', 'Mississippi', 'Missouri', 'Montana', 'Nebraska', 'Nevada', 'New Hampshire', 'New Jersey', 'New Mexico', 'New York', 'North Carolina', 'North Dakota', 'Ohio', 'Oklahoma', 'Oregon', 'Pennsylvania', 'Rhode Island', 'South Carolina', 'South Dakota', 'Tennessee', 'Texas', 'Utah', 'Vermont', 'Virginia', 'Washington', 'West Virginia', 'Wisconsin', 'Wyoming']

weekday_names

Returns array consisting of weekday names.

['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']

weekday_names_short

Returns array consisting of weekday first 3 chars of names

['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']

wrap

Wraps the value with wrap string

Expression	Result
`wrap('name','start-','-end')`✏️	`start-name-end`
`wrap('name','start')`✏️	`startnamestart`
`wrap('','start','end')`✏️	`<blank>`

wrap_bracket

Calls wrap with [ and ] as start and end guards

Expression	Result
`wrap_bracket('abc')`✏️	`[abc]`
`wrap_bracket('')`✏️	`<blank>`
`wrap_bracket(null)`✏️	`<null>`

zero_if_blank

If value is zero, returns null else returns value as is.

Expression	Result
`zero_if_blank('0')`✏️	`0`
`zero_if_blank('abc')`✏️	`abc`

zero_if_null

If value is zero, returns null else returns value as is.

Expression	Result
`zero_if_null('')`✏️	`<blank>`
`zero_if_null('abc')`✏️	`abc`
`zero_if_null(0)`✏️	`0`

sum

sum(...values: number[]): number

Calculates the sum of the provided numbers.

Accepts a variable number of arguments or an array of numbers.
If no values are provided, the function returns 0.
Non-numeric values are converted to 0 before summation.

Expression	Result
`sum(1, 2, 3, 4)`✏️	`10`
`sum(1, -2, 3.5)`✏️	`2.5`
`sum([])`✏️	`0`
`sum(null, 5, undefined, 10)`✏️	`15`

cumulative_sum

cumulative_sum(...values: number[]): number[]

Calculates the cumulative sum of the provided numbers.

Accepts a variable number of arguments or an array of numbers.
Returns an array where each element is the sum of all preceding values, including the current one.
Non-numeric values are converted to 0 before calculation.
If no values are provided, the function returns 0.

Expression	Result
`cumulative_sum(1, 2, 3, 4)`✏️	`[1,3,6,10]`
`cumulative_sum(1, -2, 3.5)`✏️	`[1,-1,2.5]`
`cumulative_sum([])`✏️	`0`
`cumulative_sum(null, 5, undefined, 10)`✏️	`[0,5,5,15]`

relative_date

relative_date(value: number, unit: DateUnit): Date

Calculates a date relative to the current date (now) by adding the specified value of the given unit.

Uses the current date (now) as the reference.
The unit specifies the type of date adjustment, such as days, months, or years.

Expression	Result
`relative_date(5, days)`✏️	`2025-01-30T17:18:34.291Z`
`relative_date(-1, months)`✏️	`2025-01-30T17:18:34.291Z`
`relative_date(1, years)`✏️	`2025-01-30T17:18:34.292Z`
`relative_date(0, days)`✏️	`2025-01-30T17:18:34.292Z`

compare_date

compare_date(inputDate: any, compareDate: any): number

Compares two dates (inputDate and compareDate) and returns a numerical value indicating their relative order.

Returns -1 if inputDate is earlier than compareDate.
Returns 1 if inputDate is later than compareDate.
Returns 0 if both dates are the same.

Expression	Result
`compare_date('2024-11-01', '2024-11-05')`✏️	`-1`
`compare_date('2024-11-05', '2024-11-01')`✏️	`1`
`compare_date('2024-11-01', '2024-11-01')`✏️	`0`
`compare_date(null, '2024-11-01')`✏️	`0`

compare_number

compare_number(val1: any, val2: any): number

Compares two values (val1 and val2) as numbers and returns a numerical value indicating their relative order.

Returns -1 if val1 is less than val2. Returns 1 if val1 is greater than val2. Returns 0 if both values are equal.

Expression	Result
`compare_number(5, 10)`✏️	`-1`
`compare_number(10, 5)`✏️	`1`
`compare_number(5, 5)`✏️	`0`
`compare_number('5.5', '10')`✏️	`-1`

contains_any

contains_any(input: RealAny, matches: any | any[]): boolean

Checks if any element in matches is found within input. It compares the values in a case-insensitive manner using the containsIc function.

If either input or matches is null or undefined, the function returns false.
If matches is an array, it will check if any element in matches is contained in input.
If matches is a single value, it will check if that value is contained in input.

Expression	Result
`contains_any('apple', ['banana', 'apple'])`✏️	`true`
`contains_any('apple', 'apple')`✏️	`true`
`contains_any(['apple', 'orange'], 'apple')`✏️	`true`
`contains_any('apple', 'Orange')`✏️	`false`
`contains_any('apple', ['banana', 'orange'])`✏️	`false`
`contains_any(null, 'apple')`✏️	`false`
`contains_any('apple', null)`✏️	`false`

count_matches

count_matches(main_str: string, sub_str: string): number

Counts the number of occurrences of sub_str within main_str. If either main_str or sub_str is null or undefined, it returns 0.

If either main_str or sub_str is null or undefined, the function returns 0.
It performs a case-sensitive count of how many times sub_str appears within main_str.
If sub_str is an empty string, the function returns 0.

Expression	Result
`count_matches('apple apple orange', 'apple')`✏️	`2`
`count_matches('apple apple orange', 'banana')`✏️	`0`
`count_matches('apple', 'apple')`✏️	`1`
`count_matches('apple apple apple', '')`✏️	`18`
`count_matches('apple', null)`✏️	`0`
`count_matches(null, 'apple')`✏️	`0`

contains_whitespaces

contains_whitespaces(str: string): boolean

Checks if the provided string str contains any whitespace characters (spaces, tabs, newlines, etc.).

Returns true if str contains any whitespace characters.
Returns false if str does not contain any whitespace characters.
Uses a regular expression (\s) to test for whitespace.

Expression	Result
`contains_whitespaces('apple orange')`✏️	`true`
`contains_whitespaces('appleorange')`✏️	`false`
`contains_whitespaces(' ')`✏️	`true`
`contains_whitespaces('apple\norange')`✏️	`true`
`contains_whitespaces('apple\torange')`✏️	`true`

trim_to_null

trim_to_null(val: any): any

Trims the input value val and returns null if the trimmed value is blank (i.e., empty string or whitespace). Otherwise, returns the trimmed value.

If val is null or undefined, it returns null.
If val is a string that, after trimming, is blank (i.e., empty or only contains whitespace), it returns null.
Otherwise, it returns the trimmed version of val.

Expression	Result
`trim_to_null(' apple ')`✏️	`apple`
`trim_to_null(' ')`✏️	`<null>`
`trim_to_null('apple')`✏️	`apple`
`trim_to_null('')`✏️	`<null>`
`trim_to_null(null)`✏️	`<null>`
`trim_to_null(undefined)`✏️	`<null>`

trim_to_blank

trim_to_blank(val: any): any

Trims the input value val and returns an empty string if the trimmed value is null. Otherwise, it returns the trimmed value.

If val is null, it returns an empty string ("").
If val is undefined, it also returns an empty string ("").
If val is a string, it trims the string and returns the result.
If the result after trimming is null, it returns an empty string ("").

Expression	Result
`trim_to_blank(' apple ')`✏️	`apple`
`trim_to_blank(' ')`✏️	`<blank>`
`trim_to_blank('apple')`✏️	`apple`
`trim_to_blank('')`✏️	`<blank>`
`trim_to_blank(null)`✏️	`<blank>`
`trim_to_blank(undefined)`✏️	`<blank>`

to_number_or_zero

to_number_or_zero(input: RealAny): number

Converts the input input to a number. If the input cannot be converted to a valid number, it returns 0.

If input is already a valid number, it returns input.
If input is a string, it attempts to parse it as a number. If the string is a valid number, it returns the parsed number; otherwise, it returns 0.
If input is of any other type (including null, undefined, or objects), it returns 0.

Expression	Result
`to_number_or_zero(10)`✏️	`10`
`to_number_or_zero('10.5')`✏️	`10.5`
`to_number_or_zero('not a number')`✏️	`0`
`to_number_or_zero('')`✏️	`0`
`to_number_or_zero(null)`✏️	`0`
`to_number_or_zero(undefined)`✏️	`0`
`to_number_or_zero(true)`✏️	`0`

is_null

is_null(input: RealAny): boolean

Checks whether the provided input is null or undefined.

Returns true if input is null or undefined.
Returns false if input is any other value.

Expression	Result
`is_null(null)`✏️	`true`
`is_null(undefined)`✏️	`true`
`is_null(10)`✏️	`false`
`is_null('string')`✏️	`false`
`is_null([1, 2, 3])`✏️	`false`

to_date

to_date(year: number, month: number, dayOfMonth: number): string

Converts the given year, month, and dayOfMonth into an ISO date string in the format YYYY-MM-DD.

Returns an ISO date string (YYYY-MM-DD) for the provided year, month, and day.
If any invalid or missing date values are provided, the toIsoDate function will handle the conversion or throw an error.

Expression	Result
`to_date(2024, 11, 26)`✏️	`2024-11-26`
`to_date(2023, 5, 15)`✏️	`2023-05-15`
`to_date(2020, 2, 29)`✏️	`2020-02-29`

to_datetime

to_datetime(year: number, month: number, dayOfMonth: number, hour?: number, minute?: number, second?: number, ms?: number): string

Converts the provided year, month, dayOfMonth, hour, minute, second, and ms into an ISO 8601 date-time string.

Returns the ISO date-time string for the given values in the format YYYY-MM-DDTHH:mm:ssZ.
The time portion defaults to 00:00:00.000 if not provided.
The returned date-time string is in UTC (Z suffix indicating UTC).

Expression	Result
`to_datetime(2024, 11, 26)`✏️	`2024-11-26T00:00:00Z`
`to_datetime(2023, 5, 15, 14, 30)`✏️	`2023-05-15T14:30:00Z`
`to_datetime(2020, 2, 29, 23, 59, 59)`✏️	`2020-02-29T23:59:59Z`
`to_datetime(2021, 8, 15, 12, 0, 0, 500)`✏️	`2021-08-15T12:00:00.500Z`

replace

replace(str: string, search: string, replace?: string): string

Replaces all occurrences of search within the str with the specified replace string. If replace is not provided, it defaults to an empty string.

If str or search is null (undefined or null), the function returns the original str.
If replace is not provided, it defaults to an empty string, effectively removing the search substring from str.

Expression	Result
`replace('hello world', 'world', 'there')`✏️	`hello there`
`replace('test test test', 'test', 'done')`✏️	`done done done`
`replace('hello', 'o', 'a')`✏️	`hella`
`replace('abcabcabc', 'abc', '')`✏️	`<blank>`

replace_recurse

replace_recurse(str: string, search: string, replaceValue?: string, times?: number): string

Recursively replaces all occurrences of search within the str with the specified replaceValue. The function will stop replacing after the specified number of times, or continue indefinitely if times is not provided.

If str or search is null (undefined or null), the function returns the original str.
If times is not provided, the function will replace all occurrences of search in str.
If times is provided, the function will replace up to the specified number of occurrences.

Expression	Result
`replace_recurse('hello world hello', 'hello', 'hi', 1)`✏️	`hi world hi`
`replace_recurse('aaa aaa aaa', 'aaa', 'xyz', 2)`✏️	`xyz xyz xyz`
`replace_recurse('no replacement here', 'test', 'done')`✏️	`no replacement here`
`replace_recurse('a b c d e', 'e', 'z')`✏️	`a b c d z`

replace_any

replace_any(str: string, searchReplaceArray: string[]): string

Replaces occurrences of substrings in str based on a pair of search and replace values in searchReplaceArray. Each pair of values in searchReplaceArray represents a search string and its corresponding replace string.

If str is null (undefined or null), the function returns the original str.
If searchReplaceArray is empty or contains an odd number of elements, no replacements are made.
For each pair of elements in searchReplaceArray, the function replaces occurrences of the first element (search) with the second element (replace).

Expression	Result
`replace_any('hello world', ['hello', 'hi', 'world', 'there'])`✏️	`hi there`
`replace_any('abc def ghi', ['abc', 'xyz', 'def', 'uvw'])`✏️	`xyz uvw ghi`
`replace_any('no replacement', [])`✏️	`no replacement`
`replace_any('apple banana apple', ['apple', 'orange', 'banana', 'grape'])`✏️	`orange grape orange`

replace_any_recurse

replace_any_recurse(str: string, searchReplaceArray: string[], times: number): string

Recursively replaces occurrences of substrings in str based on pairs of search and replace values in searchReplaceArray. The replacement is performed repeatedly until no more replacements are possible or the specified number of iterations (times) is reached.

If str is null (undefined or null), the function returns the original str.
If searchReplaceArray is empty, the function returns str without performing any replacements.
The searchReplaceArray should contain pairs of search and replace strings. If the array contains an odd number of elements, the last element is ignored.
If times is not specified or is negative, replacements are applied indefinitely until no more replacements are possible.

Expression	Result
`replace_any_recurse('hello hello world', ['hello', 'hi', 'world', 'there'], 1)`✏️	`hi hi there`
`replace_any_recurse('abc def ghi', ['abc', 'xyz', 'def', 'uvw'], 2)`✏️	`xyz uvw ghi`
`replace_any_recurse('no replacement', [], 5)`✏️	`no replacement`
`replace_any_recurse('apple banana apple', ['apple', 'orange', 'banana', 'grape'], -1)`✏️	`orange grape orange`
`replace_any_recurse('looping', ['loop', 'lo'], 3)`✏️	`loing`

extract_digits

extract_digits(str: any): string

Extracts all numeric digits from the input string str and returns them as a single concatenated string.

If str is null (undefined or null), the function returns str as is.
Removes all non-digit characters from the input.

Expression	Result
`extract_digits('abc123def456')`✏️	`123456`
`extract_digits('Phone: +1 (555) 123-4567')`✏️	`15551234567`
`extract_digits('NoDigitsHere!')`✏️	`<blank>`
`extract_digits('123.456,789')`✏️	`123456789`
`extract_digits(null)`✏️	`<null>`

index_of

index_of(str: any, substr: any, startPos?: number): number

Finds the position of the first occurrence of a substring substr within the string str, starting the search at the optional position startPos.

If either str or substr is null (undefined or null), the function returns -1.
The search is case-insensitive.
Converts both str and substr to strings before performing the search.

Expression	Result
`index_of('Hello, World!', 'world')`✏️	`7`
`index_of('abcABCabc', 'ABC', 3)`✏️	`3`
`index_of('JavaScript', 'script')`✏️	`4`
`index_of('CaseInsensitive', 'CASE')`✏️	`0`
`index_of(null, 'test')`✏️	`-1`

join_psv

join_psv(values: any): string

Joins the elements of values into a pipe-separated (|) string.

Uses the join function with | as the separator.
If values is not an array, it is coerced to a string and returned as-is (enclosed in optional start and end if specified).
Returns an empty string if values is null.

Expression	Result
`join_psv(['apple', 'banana', 'cherry'])`✏️	`apple\|banana\|cherry`
`join_psv(['1', '2', '3', '4'])`✏️	`1\|2\|3\|4`
`join_psv([])`✏️	`<blank>`
`join_psv(null)`✏️	`<blank>`
`join_psv('single')`✏️	`single`

join

join(values: any, separator?: string, start?: string, end?: string): string

Joins the elements of values into a single string using the specified separator, and optionally encloses the result with start and end.

If values is not an array, it is coerced to a string and returned with start and end appended.
If values is null, the function returns an empty string.
Default separator is , if not provided.

Expression	Result
`join(['apple', 'banana', 'cherry'])`✏️	`apple,banana,cherry`
`join(['1', '2', '3', '4'], '\|')`✏️	`1\|2\|3\|4`
`join([], ',')`✏️	`<blank>`
`join(null)`✏️	`<blank>`
`join('single', '-', '(', ')')`✏️	`(single)`

join_bracket

join_bracket(values: any, separator: string): string

Joins the elements of values into a single string using the specified separator, and encloses the result within square brackets ([ ]).

If values is not an array, it is coerced to a string and enclosed in square brackets.
If values is null, the function returns an empty pair of brackets ([]).

Expression	Result
`join_bracket(['apple', 'banana', 'cherry'], ',')`✏️	`[apple,banana,cherry]`
`join_bracket(['1', '2', '3'], '\|')`✏️	`[1\|2\|3]`
`join_bracket([], ';')`✏️	`[]`
`join_bracket(null, ',')`✏️	`<blank>`
`join_bracket('single', '-')`✏️	`[single]`

join_curly

join_curly(values: any, separator: string): string

Joins the elements of values into a single string using the specified separator, and encloses the result within curly braces ({ }).

If values is not an array, it is coerced to a string and enclosed in curly braces.
If values is null, the function returns an empty pair of braces ().

Expression	Result
`join_curly(['apple', 'banana', 'cherry'], ',')`✏️	`{apple,banana,cherry}`
`join_curly(['1', '2', '3'], '\|')`✏️	`{1\|2\|3}`
`join_curly([], ';')`✏️	`{}`
`join_curly(null, ',')`✏️	`<blank>`
`join_curly('single', '-')`✏️	`{single}`

normalize_ws

normalize_ws(str: any): string

Normalizes the whitespace in a given string by:

Replacing all sequences of whitespace (spaces, tabs, newlines) with a single space.
Trimming leading and trailing whitespace.
If the input str is null, the function returns str as is.

Expression	Result
`normalize_ws(' This is a test ')`✏️	`This is a test`
`normalize_ws('Line\nBreaks\tand Spaces')`✏️	`Line Breaks and Spaces`
`normalize_ws('NoExtraSpaces')`✏️	`NoExtraSpaces`
`normalize_ws(' Leading and trailing ')`✏️	`Leading and trailing`
`normalize_ws(null)`✏️	`<null>`

is_date

is_date(input: RealAny): boolean

Checks if the input is a valid date.

Returns true if the date is valid, otherwise returns false.

Expression	Result
`is_date('2024-11-26')`✏️	`true`
`is_date('Invalid Date')`✏️	`false`
`is_date(undefined)`✏️	`false`

remove_suffix_recurse

remove_suffix_recurse(input: RealAny, suffixes: string | string[]): RealAny

Removes a suffix (or multiple suffixes) from the input string recursively.

The function checks if the input ends with any of the specified suffixes.
If it does, it removes the suffix and repeats the process until no suffix matches.
If the input or suffixes are null, the input is returned unchanged.

Expression	Result
`remove_suffix_recurse('testfile.txt', ['.txt', '.pdf'])`✏️	`testfile`
`remove_suffix_recurse('filename.csv', '.csv')`✏️	`filename`
`remove_suffix_recurse('document.pdf', '.txt')`✏️	`document.pdf`

remove_prefix_recurse

remove_prefix_recurse(input: RealAny, prefixs: string | string[]): RealAny

Removes a prefix (or multiple prefixes) from the input string recursively.

The function checks if the input starts with any of the specified prefixes.
If it does, it removes the prefix and repeats the process until no prefix matches.
If the input or prefixes are null, the input is returned unchanged.

Expression	Result
`remove_prefix_recurse('prefile.txt', ['pre', 'sub'])`✏️	`file.txt`
`remove_prefix_recurse('abc123', 'abc')`✏️	`123`
`remove_prefix_recurse('testinput', ['xyz', 'test'])`✏️	`input`

remove_end

remove_end(input: RealAny, length = 1): RealAny

Removes a specified number of characters or elements from the end of the input.

The function checks if the input is null or NaN, returning null if so. If the length is less than or equal to 0, it returns the input unchanged. If the input is an array, it removes the specified number of elements from the end of the array. If the input is a string, it removes the specified number of characters from the end of the string.

Expression	Result
`remove_end('hello world', 5)`✏️	`hello`
`remove_end([1, 2, 3, 4], 2)`✏️	`[1,2]`
`remove_end('example', 3)`✏️	`exam`
`remove_end([10, 20, 30], 1)`✏️	`[10,20]`

remove_start

remove_start(input: RealAny, length = 1): RealAny

Removes a specified number of characters or elements from the start of the input.

The function checks if the input is null or NaN. If so, it returns null.
If the length is less than or equal to 0, it returns the input unchanged.
If the input is an array, it removes the specified number of elements from the start of the array.
If the input is a string, it removes the specified number of characters from the start of the string.

Expression	Result
`remove_start('hello world', 5)`✏️	`world`
`remove_start([1, 2, 3, 4], 2)`✏️	`[3,4]`
`remove_start('example', 3)`✏️	`mple`
`remove_start([10, 20, 30], 1)`✏️	`[20,30]`

error_if_zero

error_if_zero(input: RealAny, message: string): RealAny

Checks if the input value is zero or blank and throws an error with the provided message if it is.

The function checks if the input value is zero or blank (i.e., an empty string, null, or undefined).
If the input is zero or blank, the function throws an error with the provided message.
If the input is not zero, it returns the input unchanged.

Expression	Result
`error_if_zero(0, 'Value cannot be zero')`✏️	`Error: Value cannot be zero`
`error_if_zero('', 'Input is blank')`✏️	`Error: Input is blank`
`error_if_zero(5, 'This will not be thrown')`✏️	`5`

error_if_null

error_if_null(input: RealAny, message: string): RealAny

Checks if the input value is null and throws an error with the provided message if it is.

The function checks if the input value is null.
If the input is null, the function throws an error with the provided message.
If the input is not null, it returns the input unchanged.

Expression	Result
`error_if_null(null, 'Value cannot be null')`✏️	`Error: Value cannot be null`
`error_if_null(undefined, 'This will not throw an error')`✏️	`Error: This will not throw an error`
`error_if_null('some value', 'No error here')`✏️	`some value`

error_if_blank

error_if_blank(input: RealAny, message: string): RealAny

Checks if the input value is blank (either null, undefined, or an empty string/array) and throws an error with the provided message if it is.

If the input is blank, the function throws an error with the provided message.
If the input is not blank, it returns the input unchanged.

Expression	Result
`error_if_blank('', 'Value cannot be blank')`✏️	`Error: Value cannot be blank`
`error_if_blank(null, 'This will throw an error')`✏️	`Error: This will throw an error`
`error_if_blank(undefined, 'Another error')`✏️	`Error: Another error`
`error_if_blank('some value', 'No error here')`✏️	`some value`

error_if_not_blank

error_if_not_blank(input: RealAny, message: string): RealAny

Checks if the input value is not blank (i.e., not null, undefined, or an empty string/array) and throws an error with the provided message if it is.

If the input is not blank, the function throws an error with the provided message.
If the input is blank, it returns the input unchanged.

Expression	Result
`error_if_not_blank('some value', 'This will throw an error')`✏️	`Error: This will throw an error`
`error_if_not_blank('', 'No error here')`✏️	`<blank>`
`error_if_not_blank(null, 'No error here either')`✏️	`<null>`
`error_if_not_blank(undefined, 'No error here too')`✏️	`<null>`

null_if_not_blank

null_if_not_blank(input: RealAny): RealAny

Returns null if the input is not blank, otherwise returns the input unchanged.

If the input is not blank, it returns null.
If the input is blank (i.e., null, undefined, or an empty string/array), it returns the input unchanged.

Expression	Result
`null_if_not_blank('some value')`✏️	`<null>`
`null_if_not_blank('')`✏️	`<blank>`
`null_if_not_blank(null)`✏️	`<null>`
`null_if_not_blank(undefined)`✏️	`<null>`

error_if_not_null

error_if_not_null(input: RealAny, message: string): RealAny

Throws an error if the input is not null, otherwise returns the input unchanged.

If the input is not null, it throws an error with the specified message.
If the input is null, it returns the input unchanged.

Expression	Result
`error_if_not_null('value', 'Input cannot be null')`✏️	`Error: Input cannot be null`
`error_if_not_null(null, 'Input cannot be null')`✏️	`<null>`
`error_if_not_null(undefined, 'Input cannot be null')`✏️	`<null>`

to_json

to_json(val: any): string | undefined

Converts a JavaScript value to a JSON string. If the conversion fails, it throws an error with a detailed message.

The function first checks if the input val is null. If val is null, it returns val unchanged.
If val is not null, it attempts to convert the value to a JSON string using JSON.stringify.
If the conversion fails (e.g., due to circular references or unsupported types), it throws an error with a message that includes the error details.

Expression	Result
`to_json()`✏️	`<null>`
`to_json([1, 2, 3, 4])`✏️	`[1,2,3,4]`
`to_json(undefined)`✏️	`<null>`
`to_json({ circular: this })`✏️	`{"circular":{"$record":{},"$temp":{},"dbl9whlzbv":true}}`

set_value

set_value(obj: any, path: string, value: any): any

Sets a value at a specified path within an object. If the path is blank, it returns the object unchanged.

The function first checks if the obj is falsy (null or undefined). If it is, it initializes obj as an empty object.
It then checks if the path is blank (empty or undefined). If the path is blank, it simply returns the object without modifying it.
If the path is valid, it normalizes the path to lowercase and uses _set to set the value at the specified path in the object.
Finally, it returns the updated object.

Expression	Result
`set_value({ 'user': { 'name': 'Alice' } }, 'user.name', 'Bob')`✏️	`{"user":{"name":"Bob"}}`
`set_value({}, 'address.city', 'New York')`✏️	`{"address":{"city":"New York"}}`
`set_value({ 'config': {} }, 'config.setting', true)`✏️	`{"config":{"setting":true}}`
`set_value({ }, '', 'default value')`✏️	`{}`

set_values

set_values(obj: any, values: any): any

Sets multiple values on an object at the specified paths. If the values is not provided or is not an object, it returns the original object unchanged.

The function first checks if the obj is falsy (null or undefined). If it is, it initializes obj as an empty object.
It then checks if values is either null or not an object. If it's not a valid object, it returns the original obj.
If values is valid, the function loops through each key in the values object.
For each key-value pair in values, it calls the set_value function to set the corresponding value on the obj at the key path.
Finally, the function returns the updated object.

Expression	Result
`set_values({ 'user': { 'name': 'Alice' } }, { 'user.name': 'Bob', 'user.age': 30 })`✏️	`{"user":{"name":"Bob","age":30}}`
`set_values({}, { 'address.city': 'New York', 'address.zip': '10001' })`✏️	`{"address":{"city":"New York","zip":"10001"}}`
`set_values({ }, { 'config.setting': true, 'config.theme': 'dark' })`✏️	`{"config":{"setting":true,"theme":"dark"}}`
`set_values({ }, null)`✏️	`{}`

left

left(str: any, len: number): string Returns the leftmost len characters of a string str or the entire string if its length is smaller than len.

If the input string (str) is null or undefined, the function returns null.
If the input string is blank or if len is less than 0, the function returns an empty string.
Otherwise, the function converts the input to a string (if it is not already a string) and returns a substring starting from the beginning and ending at the specified len.
Expression Result
left('Hello, World!', 5)✏️
Hello
left('OpenAI', 3)✏️
Ope
left('Leftmost', 8)✏️
Leftmost
left('Example', -1)✏️
<blank>
left(null, 4)✏️
<null>

last

last(input: RealAny, length?: number | any): RealAny Extracts the last length characters (or elements) of the input.

If the input is null, undefined, or NaN, the function returns null.
If the input is an object, it is returned unchanged.
If length is not provided, it defaults to 1.
If the input is an array:
- Returns the last length elements as an array.
- If length is 1, a single element (not wrapped in an array) is returned.
- If length is 0, an empty array is returned.
If the input is a string:
- Returns a substring containing the last length characters.
  Expression Result
  last('Hello, World!', 5)✏️
  orld!
  last('Example', 0)✏️
  <blank>
  last([1, 2, 3, 4], 2)✏️
  [3,4]
  last([1, 2, 3, 4], 1)✏️
  4
  last(null, 3)✏️
  <null>

split_regex

split_regex(input: RealAny, separator: any): string[] Splits the given input into an array of substrings based on a specified separator, which can be a string or a regular expression.

If the input is null or undefined, the function returns an empty array ([]).
If the separator is null or undefined, the function treats the entire input as a single substring and returns it in an array.

Converts the input to a string (if not already) and splits it using the separator.

Expression	Result
`split_regex('a,b,c', ',')`✏️	`["a","b","c"]`
`split_regex('a\|b\|c', '\\\|')`✏️	`["a\|b\|c"]`
`split_regex('word1 word2 word3', '\\s')`✏️	`["word1 word2 word3"]`
`split_regex('NoSeparatorHere', null)`✏️	`["NoSeparatorHere"]`
`split_regex(null, ',')`✏️	`[]`

hash_md5

hash_md5(input: RealAny): string Generates an MD5 hash for the given input.

Expression	Result
`hash_md5('hello')`✏️	`5d41402abc4b2a76b9719d911017c592`
`hash_md5(12345)`✏️	`827ccb0eea8a706c4c34a16891f84e7b`
`hash_md5(null)`✏️	`d41d8cd98f00b204e9800998ecf8427e`
`hash_md5('')`✏️	`d41d8cd98f00b204e9800998ecf8427e`
`hash_md5()`✏️	`d41d8cd98f00b204e9800998ecf8427e`

filter_blanks

filter_blanks(input: RealAny): RealAny[] Filters out non-blank values from the input, returning an array containing only blank elements.

Converts the input into an array.
Applies the is_blank function to filter elements, retaining only those considered blank.
Blanks are typically null, undefined, empty strings, or other values defined as blank by the is_blank function.
Expression Result
filter_blanks(['', null, 'hello', undefined, 0])✏️
["",null,null]
filter_blanks([1, '', 'test', ' '])✏️
[""," "]
filter_blanks('not an array')✏️
[]
filter_blanks([])✏️
[]

filter_not_blanks

filter_not_blanks(input: RealAny): RealAny[] Filters out blank values from the input, returning an array containing only non-blank elements.

Converts the input into an array using the to_array utility function.
Applies the is_not_blank function to filter elements, retaining only those considered non-blank.

Non-blank values are typically any values not defined as blank by the is_not_blank function.

Expression	Result
`filter_not_blanks(['', null, 'hello', undefined, 0])`✏️	`["hello",0]`
`filter_not_blanks([1, '', 'test', ' '])`✏️	`[1,"test"]`
`filter_not_blanks('not an array')`✏️	`["not an array"]`
`filter_not_blanks([])`✏️	`[]`

filter_nulls

filter_nulls(input: RealAny): RealAny[] Filters out non-null values from the input, returning an array containing only null elements.

Converts the input into an array using the to_array utility function.
Applies the is_null function to identify and retain only null elements.
Null values are typically those explicitly set to null.
Expression Result
filter_nulls(['a', null, undefined, 5, null])✏️
[null,null,null]
filter_nulls([null, 1, 2, null])✏️
[null,null]
filter_nulls('not an array')✏️
[]
filter_nulls([])✏️
[]

filter_not_nulls

filter_not_nulls(input: RealAny): RealAny[] Filters out null values from the input, returning an array containing only non-null elements.

Converts the input into an array using the to_array utility function.
Applies the is_not_null function to identify and retain elements that are not null.

Non-null values include any element not explicitly set to null.

Expression	Result
`filter_not_nulls(['a', null, undefined, 5, null])`✏️	`["a",5]`
`filter_not_nulls([null, 1, 2, null])`✏️	`[1,2]`
`filter_not_nulls('not an array')`✏️	`["not an array"]`
`filter_not_nulls([])`✏️	`[]`

substring

substring(value: any, start?: number, end?: number): string | null Extracts a substring from the input string, starting and ending at specified indices. Handles negative indices and ensures boundaries are respected.

Converts the input value to a string if it's not already one.
If start is not provided, defaults to 0 (beginning of the string).
If end is not provided, defaults to the string's length.
Negative indices are adjusted to count backward from the end of the string.
Adjusts end if it exceeds the string's length.

Returns an empty string if start is greater than end.

Expression	Result
`substring('hello world', 0, 5)`✏️	`hello`
`substring('hello world', -5)`✏️	`world`
`substring('hello world', 0, -6)`✏️	`hello`
`substring('hello', 10, 15)`✏️	`<blank>`
`substring(null, 2, 4)`✏️	`<null>`

substring_before

substring_before(str: any, separator: any): string | null Returns the portion of the input string that appears before the first occurrence of the specified separator.

Converts the input and separator to lowercase strings for case-insensitive comparison.
If the separator is not found, the function returns the original string.
If the separator is blank or empty, the function returns an empty string.