Frequently used mySQL Calculations

Calculation Fields use mySQL to perform calculations.
The following are examples of frequently used calculations for the Table by the Field type they are using:

CASE()
The CASE function in SQL is used to evaluate a condition and return a different value depending on the outcome.
The condition can be any valid SQL expression. If the condition is evaluated as TRUE, the corresponding value is returned. If the condition is evaluated as FALSE, the next condition is evaluated. If none of the conditions are evaluated to TRUE, the default_value is returned.

• • CASE
  WHEN @{Support Plan} = 'Silver' THEN 600
  WHEN @{Support Plan} = 'Gold' THEN 1200
  WHEN @{Support Plan} = 'Platinum' THEN 3600
  ELSE 0
END

Text Fields

CONCAT()
The CONCAT function is used to concatenate, or join together, two or more strings. It takes an arbitrary number of string arguments and returns a new string containing the concatenation of all of the arguments.

• • name = "Bard";
greeting = concat("Hello, ", name, "!");
print greeting; // Outputs "Hello, Bard!"

URL Fields and Links

JSON_EXTRACT()
This function extracts a value from a JSON object. The function takes two arguments: the JSON object and the path to the value. The path is a string that specifies the location of the value in the JSON object.

• • JSON_EXTRACT(json_object, '$.url')

For example, the following code will extract the url field from the JSON object {"url": "https://google.com", "title": "Google"}:

JSON_UNQUOTE()
This function removes the quotation marks from a JSON string. The function takes one argument: the JSON string.

• • JSON_UNQUOTE('"https://google.com"')

For example, the following code will remove the quotation marks from the JSON string "https://google.com":