Core Functions

The abs(X) function returns the absolute (non-negative) value of X.

Example: Handling Negative Prices

If you receive transaction data where some prices are incorrectly stored as negative values, you can use abs(X) to ensure all prices are positive.

ABS({{order.price}})

Use Case in a Formula

If an order price is -50, the function will return 50.

The ceil(X) function rounds X up to the smallest integer greater than or equal to X. This can be helpful when working with quantities that need to be rounded up (e.g., rounding up fractional amounts in an order).

Example: Rounding Up to the Next Whole Number for Order Quantity

If you’re calculating the number of boxes required to ship a certain number of items, and you need to round up the result to ensure you have enough boxes, you can use:

CEIL({{order.quantity}}/{{box.capacity}})

Use Case in a Formula

If order.quantity = 53 and box.capacity = 10, the formula will return 6 (because you need 6 boxes to ship 53 items).

The floor(X) function rounds X down to the largest integer less than or equal to X. This is useful when you need to remove fractional values and keep only the whole number portion.

Example: Calculating Full Discounted Items in Bulk Orders

In an E-commerce scenario, let’s say a store offers a “Buy 3, Get 1 Free” promotion. To determine how many free items a customer gets, you can use:

FLOOR({{order.quantity}}/3)

Use Case in a Formula

If order.quantity = 10, the result would be 3, meaning the customer gets 3 free items. The remaining item does not qualify for the promotion.

The max(X,Y,…) function returns the highest value among the given arguments.

Example: Determining the Highest Order Value

If you want to compare multiple product prices in an order and select the highest price, you can use:

MAX({{order.item1_price}},{{order.item2_price}},{{order.item3_price}})

Use Case in a Formula

If item1_price = 10, item2_price = 25, and item3_price = 15, the function will return 25.

The min(X,Y,…) function returns the smallest value among the given arguments.

Example: Finding the Lowest Shipping Cost

If an order has multiple shipping options and you want to select the cheapest one, you can use:

MIN({{order.shipping_option1}},{{order.shipping_option2}},{{order.shipping_option3}})

Use Case in a Formula

If shipping_option1 = 5.99, shipping_option2 = 3.49, and shipping_option3 = 7.00, the function will return 3.49 as the lowest shipping cost.

The pi function returns the mathematical constant π (approximately 3.14159265358979).

Example: Calculating the Circumference of a Circle

If you need to calculate the circumference of a circle with a given radius, you can use the formula:

2*PI()*{{circle.radius}}

Use Case in a Formula

If radius = 5, the formula will calculate the circumference as 31.4159265358979.

The randint(X,Y) function generates a random integer between X and Y, inclusive. This is useful when you need a random number within a specific range.

Example: in E-commerce, randomly Selecting a Discount Coupon Code

Create a list or array containing all the available discount coupon codes.

For example:

["DISCOUNT10","SUMMER20","WELCOME15","FREEBIE","FLASH5"]

Use the randint() function to generate a random integer within the range of 0 to the number of coupons in the list minus 1.

If there are 5 coupons in the list:

randint(0,4)

would generate a random number between 0 and 4.

Use the randomly generated index to select the corresponding coupon code from the list.

The random() function returns a random integer within the full range of signed 64-bit values.

Example: Generating a Random Order ID

If you need a random number for temporary order tracking, you can use:

RANDOM()

Use Case in a Formula

This might generate values like -5328723762381 or 4823791728391. If you need only positive numbers, you can take the absolute value:

ABS(RANDOM())

The randomblob(N) function generates a random binary blob with N bytes. This is useful for creating random data, such as tokens or keys.

Example: Generating a Random API Key

If you want to generate a random binary blob to use as an API key (e.g., for secure transactions), you can use:

RANDOMBLOB(16)

Use Case in a Formula

This might return a value like 0x6f47fcb95bb9d1b2, a 16-byte random binary blob. You can then convert it into a readable format or store it as-is, depending on your needs.

The round(X) function rounds X to the nearest whole number.

Example: Rounding Order Quantity

If your system requires whole numbers for product quantities, you can round a calculated value:

ROUND({{order.estimated_quantity}})

Use Case in a Formula

If estimated_quantity = 3.7, the function will return 4. If estimated_quantity = 3.4, it will return 3.

The round(X,Y) function rounds X to Y decimal places.

Example: Formatting a Price to Two Decimal Places

If you need to ensure prices are displayed with exactly two decimal places, you can use:

ROUND({{order.total_price}},2)

Use Case in a Formula

If total_price = 25.6789, the function will return 25.68. If total_price = 25.6712, it will return 25.67.

The sign(X) function returns:

• 1 if X is positive
• 0 if X is zero
• -1 if X is negative

Example: Checking Profit or Loss

If you want to determine whether a transaction resulted in a profit, loss, or break-even, use:

SIGN({{order.profit}})

Use Case in a Formula

• If profit = 150, the function returns 1 (profit).
• If profit = -75, the function returns -1 (loss).
• If profit = 0, the function returns 0 (no profit or loss).

The zeroblob(N) function generates a binary blob of N bytes, all set to zero. This is useful when you need to create a placeholder or initialize data.

Example: Initializing a Data Field

If you want to initialize a data field with zero values, you can use:

ZEROBLOB(10)

Use Case in a Formula

This would generate a 10-byte blob filled with zeros, like 0x00000000000000000000.

The char(X1, X2, …, XN) function returns a string composed of characters that correspond to the given Unicode code points. This is useful for formatting special symbols, generating dynamic labels, or encoding data.

Example: Displaying a Star Rating Symbol (★) in Product Reviews

In an E-commerce platform, if you want to display a star symbol (★) based on a rating, you can use:

CHAR(9733)

Use Case in a Formula

If a product has a 4-star rating, you can generate a visual representation like this:

CHAR(9733) || CHAR(9733) || CHAR(9733) || CHAR(9733)

This would output: ★★★★

The concat(X, …) function joins multiple strings into a single string. This is useful for formatting product names, generating personalized messages, or constructing dynamic text fields.

Example: Creating a Personalized Order Confirmation Message

In an E-commerce system, after a customer places an order, you can generate a personalized message like this:

CONCAT("Thank you, ", {{customer.name}}, "! Your order #", {{order.id}}, " has been received.")

Use Case in a Formula

If customer.name = "Alice" and order.id = 12345, the output would be:

“Thank you, Alice! Your order #12345 has been received.”

The concat_ws(SEP, X, …) function joins multiple strings into one, using a specified separator (SEP). This is useful when formatting lists, CSV exports, or combining address components.

Example: Formatting a Shipping Address

In an E-commerce system, you can use this function to generate a properly formatted shipping address:

CONCAT_WS(",",{{customer.street}},{{customer.city}},{{customer.state}},{{customer.zipcode}},{{customer.country}})

Use Case in a Formula

If a customer’s details are:

• customer.street = "123 Main St"
• customer.city = "Los Angeles"
• customer.state = "CA"
• customer.zipcode = "90001"
• customer.country = "USA"

The output would be:
“123 Main St, Los Angeles, CA, 90001, USA”

The format(FORMAT, …) function structures a string based on a given format pattern, similar to printf in many programming languages. This is useful for formatting prices, order summaries, and custom messages.

Example: Formatting a Product Price with Two Decimal Places

In an E-commerce system, you can ensure that product prices are displayed with two decimal places:

FORMAT("$%.2f",{{product.price}})

Use Case in a Formula

If product.price = 25, the output would be:
“$25.00”

Similarly, for an order summary with multiple values:

FORMAT("Order#%d:%s-Total:$%.2f",{{order.id}},{{product.name}},{{order.total_price}})

If:

• order.id = 12345
• product.name = "Wireless Mouse"
• order.total_price = 49.99

The output would be:
“Order #12345: Wireless Mouse – Total: $49.99”

The hex(X) function converts a given value into a hexadecimal (base-16) string. This is useful for encoding data, generating unique identifiers, or handling binary information.

Example: Generating a Hexadecimal Order ID

In an E-commerce system, you might want to generate a unique hexadecimal representation of an order ID:

HEX({{order.id}})

Use Case in a Formula

If order.id = 12345, the output would be:
“3039” (since 12345 in hexadecimal is 0x3039)

This function is helpful for encoding sensitive data or working with systems that require hex-based identifiers.

The instr(X, Y) function returns the position of the first occurrence of substring Y within string X. If Y is not found, it returns 0. This is useful for checking product names, searching for specific keywords in descriptions, or validating input data.

Example: Checking if a Product Name Contains a Keyword

In an E-commerce system, you can check if a product name contains the word “Wireless”:

INSTR({{product.name}}, "Wireless")

Use Case in a Formula

If product.name = "Wireless Bluetooth Headphones", the output would be:
1 (since “Wireless” starts at position 1)

If product.name = "Bluetooth Headphones", the output would be:
0 (since “Wireless” is not found)

The length(X) function returns the number of characters in string X. This can be useful for validating inputs, formatting text, or calculating the size of product descriptions.

Example: Checking the Length of a Product Description

In an E-commerce system, you can check if a product description exceeds a certain character limit:

LENGTH({{product.description}})

Use Case in a Formula

If product.description = "This is a high-quality wireless mouse", the output would be:
34 (the number of characters in the description)

The lower(X) function converts all characters in string X to lowercase. This is useful for standardizing inputs or making case-insensitive comparisons.

Example: Converting Product Names to Lowercase for Search

In an E-commerce system, you can convert a product name to lowercase before storing or searching for it:

LOWER({{product.name}})

Use Case in a Formula

If product.name = "Wireless Headphones", the output would be:
“wireless headphones”

The upper(X) function converts all characters in string X to uppercase. This is useful for formatting names, headings, or creating consistent outputs.

Example: Formatting Coupon Code to Uppercase

In an E-commerce system, you might want to standardize the coupon code format to uppercase:

UPPER({{coupon.code}})

Use Case in a Formula

If coupon.code = "discount20", the output would be:
“DISCOUNT20”

The replace(X, Y, Z) function replaces all occurrences of substring Y with Z in string X. This is useful for cleaning up data, fixing formatting errors, or transforming text.

Example: Replacing Product Size Abbreviation

In an E-commerce system, you may need to replace the abbreviation “S” with “Small” in product descriptions:

REPLACE({{product.description}},"S","Small")

Use Case in a Formula

If product.description = "This item is available in S, M, and L sizes", the output would be:
“This item is available in Small, M, and L sizes”

• substr(X,Y,Z):
  • X: The original string.
  • Y: The starting position within X.
    • If Y is negative, count from the end of the string.
  • Z: The length of the substring.
    • If Z is omitted, extract all characters from the starting position to the end of the string.
    • If Z is negative, extract characters before the starting position.
• substring() is an alias for the substr() function in SQLite.
  • This means you can use either function interchangeably to extract a portion of a string.
• Character Indexing:
  • For strings, positions refer to individual characters (based on UTF-8).
  • For BLOBs (binary data), positions refer to bytes.

Example: Extracting the First 5 Characters of a Product ID

In an E-commerce system, you can extract the first 5 characters of a product ID:

SUBSTR({{product.id}},1,5)

Use Case in a Formula

If product.id = "ABC123456", the output would be:
“ABC12”

The trim() function removes leading and trailing whitespace from string X.

• If no Y is provided, it removes leading and trailing spaces from the input string.
• If Y is provided, it removes any characters in Y from both ends of the input string.

Use while loops to iteratively remove characters from the beginning and end of the string until no more matching characters are found.

Example: Cleaning User Input for a Product Search

In an E-commerce system, you might trim any extra spaces from a search query:

TRIM({{search.query}})

Use Case in a Formula

If search.query = " Wireless Mouse ", the output would be:
“Wireless Mouse”

• ltrim(X): Removes leading spaces from the string X.
• ltrim(X,Y): Removes any characters found in the string Y from the beginning of string X.

Use WHILE loops to iteratively remove characters from the beginning of the string until no more matching characters are found.

Example: Clean Up User Input for Product Search

In an E-commerce system, you may want to remove leading spaces from a product search query:

LTRIM({{search.query}})

Use Case in a Formula

If search.query = " wireless headphones", the output would be:
“wireless headphones”

• rtrim(X): Removes trailing spaces from the string X.
• rtrim(X,Y): Removes any characters found in the string Y from the end of string X.

Example: Formatting a Customer’s Name Before Saving

In an E-commerce system, you can remove unwanted spaces at the end of a customer’s name:

RTRIM({{customer.name}})

Use Case in a Formula

If customer.name = "John Doe ", the output would be:
“John Doe”

The soundex(X) function returns a phonetic representation of string X, useful for approximate string matching, especially for names or words that sound similar but are spelled differently.

Example: Match Customer Names with Slight Spelling Differences

In an E-commerce system, when searching for a customer by name, you can use soundex to match similar-sounding names:

SOUNDEX({{customer.name}})

Use Case in a Formula

If customer.name = "Jon" and another record has customer.name = "John", both would return the same Soundex code, enabling better search results.

The quote(X) function returns the string X enclosed in single quotes ('X'). If the string X already contains a single quote ('), it will be escaped by doubling it ('').

Example 1: Simple Quoting

quote('Wiresk')

Output:

'Wiresk'

Example 2: Handling Single Quotes in a String

quote('Wiresk's Tool')

Output:

'Wiresk''s Tool'

Notice how the single quote (') in “Wiresk’s Tool” is doubled ('') in the output to ensure proper escaping.

The printf(FORMAT,…) function formats a string based on a given template, similar to printf in C or Excel’s TEXT function. This is useful for constructing dynamic messages or reports.

Example: Format an Order Summary Message

Generate a structured message displaying an order confirmation with the order number and total amount:

PRINTF("Order #%d: Total $%.2f",{{order.id}},{{order.total}})

Use Case in a Formula

If order.id = 1052 and order.total = 45.6789, the output would be:
“Order #1052: Total $45.68”

This is useful for decoding encoded data or handling binary storage formats.

unhex(X):

• Checks if X is NULL. If so, returns NULL.
• Checks if the length of X is even (hexadecimal digits come in pairs).
• Iterates through every pair of characters in X and checks if they are valid hexadecimal digits using SIMILAR TO '[A-Fa-f0-9]{2}'. If not, returns NULL.
• Uses the decode(X, 'hex') function to convert the hexadecimal string to a BLOB.

unhex(X,Y):

• Checks if either X or Y is NULL. If so, returns NULL.
• Removes any characters from Y that are not hexadecimal digits.
• Removes all characters from X that are present in the modified Y using regexp_replace.
• Checks if the length of X is even and if all characters in X are valid hexadecimal digits.
• Uses the decode(X, 'hex') function to convert the hexadecimal string to a BLOB.

Example: Convert a Hex-Encoded Password Hash

Suppose an e-commerce system stores password hashes in hexadecimal format. To decode a stored hash:

UNHEX({{user.password_hash}})

Use Case in a Formula

If user.password_hash = "4d7950617373776f7264", the output would be:
“MyPassword” (binary representation of the original password).

The unicode(X) function returns the Unicode code point of the first character in a given string. This is useful for character encoding, text processing, or language-based operations.

Example: Get the Unicode Value of a Currency Symbol

Extract the Unicode code point for a given currency symbol:

UNICODE({{product.currency_symbol}})

Use Case in a Formula

If product.currency_symbol = "$", the output would be:
“36” (Unicode code point for the dollar sign $).

The LPAD(X,Y,Z) function pads the left side of string X with character Z until it reaches length Y. This is useful for formatting order numbers, product SKUs, or aligning text in reports.

Example: Format an Order Number with Leading Zeros

Ensure all order numbers are exactly six digits long:

LPAD({{order.id}},6,"0")

Use Case in a Formula

If order.id = "123", the output would be:
“000123” (padded with zeros to ensure a 6-digit format).

The RPAD(X,Y,Z) function pads the right side of string X with character Z until it reaches length Y. This is useful for aligning text in reports, formatting descriptions, or creating fixed-length strings.

Example: Standardize Product Codes with Trailing Dashes

Ensure all product codes are exactly 10 characters long by padding with dashes:

RPAD({{product.code}},10,"-")

Use Case in a Formula

If product.code = "A123", the output would be:
“A123——“ (padded with dashes to a 10-character length).

The capitalize(X) function converts the first letter of a string X to uppercase while keeping the rest lowercase. This is useful for formatting customer names, product titles, or addresses.

Example: Format Customer Names Properly

Ensure that customer names start with an uppercase letter:

capitalize({{customer.name}})

Use Case in a Formula

If customer.name = "john doe", the output would be:
“John doe”

The toBase64(X) function converts a string X into its Base64 encoded representation. This is commonly used for encoding data to ensure it can be safely transmitted in text form, such as when sending binary data over a network.

Example: Encode Customer’s Credit Card Info

Encode sensitive customer information to Base64 before transmitting:

toBase64({{customer.credit_card}})

Use Case in a Formula

If customer.credit_card = "4111111111111111", the output would be:
“NDExMTExMTExMTExMTEx” (Base64 encoded string).

The fromBase64(X) function decodes a Base64-encoded string X back into its original form. This is useful for decrypting data that was previously encoded for safe transmission.

Example: Decode Base64-Encoded Product Image

Retrieve the original image data from a Base64-encoded string:

fromBase64({{product.image_base64}})

Use Case in a Formula

If product.image_base64 = "iVBORw0KGgoAAAANSUhEUgAA...", the output would be:
Original image data (binary form of the image).

The if(X,Y) function returns Y if X is true; otherwise, it returns NULL.

iif(X,Y) is the alias for if(X,Y). 

Example: Check if an Order is Valid

Return a message if the order is valid:

if({{order.status}}='valid','Order is Valid')

Use Case in a Formula

If order.status = "valid", the output would be:
“Order is Valid”.
If order.status = "invalid", the output would be:
NULL.

The if(X,Y,Z) function works similarly to if(X,Y), but instead of returning NULL when X is false, it returns Z.

iif(X,Y,Z) is the alias for if(X,Y,Z). 

Example: Discount Eligibility Check

Return a message depending on whether the customer is eligible for a discount:

if({{customer.loyalty_points}}>100,'Eligible for Discount','Not Eligible')

Use Case in a Formula

If customer.loyalty_points > 100, the output would be:
“Eligible for Discount”.
If customer.loyalty_points <= 100, the output would be:
“Not Eligible”.

The ifnull(X,Y) function checks if X is NULL. If X is NULL, it returns Y, otherwise, it returns X.

Example: Handle Missing Customer Data

If the customer.address is NULL, provide a default address:

ifnull({{customer.address}},'No Address Provided')

Use Case in a Formula

If customer.address is available (not NULL), it will return the address.
If customer.address is NULL, the output would be:
“No Address Provided”.

The nullif(X,Y) function returns NULL if X is equal to Y, otherwise, it returns X.

Example: Avoid Division by Zero

Use this function to prevent a division by zero error in calculations. If denominator is zero, it will return NULL, otherwise, it will return the numerator:

nullif({{denominator}},0)

Use Case in a Formula

If denominator = 0, the output would be:
NULL.
If denominator is non-zero, it will return the value of the denominator.

The coalesce(X,Y,…) function returns the first non-NULL value from the list of arguments.

Example: Customer Contact Information

If the customer’s primary contact information is unavailable, you can use the coalesce function to fall back on secondary information.

coalesce({{customer.email}},{{customer.phone}},'No Contact Info')

Use Case in a Formula

• If customer.email is available, the output would be:
  Customer’s email.
• If customer.email is NULL and customer.phone is available, it will return:
  Customer’s phone.
• If both are NULL, it will return:
  “No Contact Info”.

The regexp_match(X,Y) function checks if the string X matches the regular expression pattern Y. It returns True if there’s a match, otherwise it returns False.

Example: Validate a Phone Number Format

You can use regexp_match to validate if the phone number follows a specific pattern, such as ensuring it’s in the format (XXX) XXX-XXXX.

regexp_match({{customer.phone}},'^\(\d{3}\)\d{3}-\d{4}$')

Use Case in a Formula

• If customer.phone matches the pattern, the result will be:
  True.
• If customer.phone does not match the pattern, it will return:
  False.

The glob(X,Y) function checks if X matches the pattern Y using glob-style pattern matching. It is case-sensitive, unlike LIKE, which is case-insensitive.

Example: Match a Product Code Pattern

You can use glob to ensure that a product code follows a specific format, such as starting with “PROD-” followed by digits.

glob({{product.code}},'PROD-*\d{4}')

Use Case in a Formula

• If product.code matches the pattern, the result will be:
  True.
• If product.code does not match the pattern, it will return:
  False.

The like(X,Y) function matches the string X against the pattern Y, and it is case-insensitive.

Example: Search for Products by Name

You can use like to check if a product name contains the word “phone”, regardless of whether it’s written in upper or lower case.

like({{product.name}},'%phone%')

Use Case in a Formula

• If product.name is “Smartphone” or “smartphone”, the result will be:
  True.
• If product.name is “Laptop”, it will return:
  False.

The like(X,Y,Z) function is used to match X against the pattern Y, where Z specifies escape characters.

Example: Search for Products Containing Special Characters

You can use this to check if a product name contains a special character, such as an underscore (“_”), by escaping it using the Z argument.

like({{product.name}},'%\_%','\\')

In this case, the backslash (\\) is used as the escape character to treat the underscore literally rather than as a wildcard.

Use Case in a Formula

• If product.name is “Product_123”, the result will be:
  True.
• If product.name is “Product123”, it will return:
  False.

The likelihood(X,Y) function provides a hint to the query optimizer about how likely it is that the condition X is true. This is used to optimize query execution.

Example: Optimize Query Based on Likely Status

You can use this function to hint that a certain status is more likely to occur, such as assuming that “available” products are more likely to be selected than “out_of_stock.”

likelihood({{product.status}} ='available',0.8)

Use Case in a Formula

The second argument (0.8) hints that this condition is likely true 80% of the time.

If product.status is “available”, the optimizer will prioritize this condition, improving query performance when searching for available products.

The likely(X) function is similar to likelihood, but it provides a hint to the optimizer that X is very likely to be true.

Example: Optimize Query for Highly Likely Orders

You can use likely to optimize a query that filters orders with a status of “pending,” assuming it’s highly likely that most orders are pending.

likely({{order.status}} ='pending')

Use Case in a Formula

• If order.status is “pending”, the optimizer will prioritize this condition, speeding up queries where this status is frequently true.

The unlikely(X) function is the opposite of likely. It suggests to the query optimizer that X is unlikely to be true, helping it make better execution plans for such cases.

Example: Optimize Query for Unlikely Events, Like Product Returns

If you have a dataset of product returns and returns are rare, you might use this to optimize queries involving return events.

unlikely({{order.returned}} = 1)

Use Case in a Formula

• If order.returned is 1 (meaning the order was returned), the optimizer will consider this condition unlikely to occur, which can help optimize the query in systems where returns are rare.

The changes() function returns the number of rows affected by the last INSERT, UPDATE, or DELETE operation performed on the database.

Example:

-- Insert three rows into a table
INSERT INTO MyTable (column1, column2) VALUES ('value1', 'value2'), ('value3', 'value4'), ('value5', 'value6');

-- Get the number of rows affected by the last INSERT statement
SELECT changes(); -- Output: 3 

-- Update a single row in the table
UPDATE MyTable SET column1 = 'new_value' WHERE id = 1;

-- Get the number of rows affected by the last UPDATE statement
SELECT changes(); -- Output: 1

Use Case:

• Tracking Data Changes: Monitor the number of rows affected by database operations for auditing, logging, or performance analysis.
• Conditional Logic: Use the changes() function within your SQL code to control the execution flow based on the number of rows affected by a previous statement.

The last_insert_rowid() function returns the row ID of the last row that was inserted into the database.

Auto-Incrementing Keys: If your table has an AUTOINCREMENT column (often an INTEGER PRIMARY KEY), last_insert_rowid() will return the value of that auto-generated key for the newly inserted row.

Example:

-- Insert a new row into the 'customers' table
INSERT INTO customers (name) VALUES ('John Doe');

-- Get the ROWID of the newly inserted row
SELECT last_insert_rowid(); 

-- Update the newly inserted row
UPDATE customers SET email = 'john.doe@example.com' WHERE rowid = last_insert_rowid();

The total_changes() function returns the total number of INSERT, UPDATE, and DELETE operations that have been executed since the database connection was opened.

Cumulative Count: Unlike changes(), which only reports the changes from the last statement, total_changes() provides the total number of rows affected by all INSERT, UPDATE, and DELETE statements executed since the connection was opened.

Connection-Specific: The count is specific to the current database connection. Changes made by other connections are not reflected in this count.

Includes Trigger Effects: Unlike changes(), total_changes() includes changes made by triggers.

Useful for Monitoring: This function is valuable for monitoring the overall activity and impact of your database operations within a given connection.

Example:

-- Insert three rows
INSERT INTO MyTable (column1, column2) VALUES ('value1', 'value2'), ('value3', 'value4'), ('value5', 'value6');

-- Get the total number of changes
SELECT total_changes(); -- Output: 3 (assuming no prior changes)

-- Update a row
UPDATE MyTable SET column1 = 'new_value' WHERE id = 1;

-- Get the total number of changes
SELECT total_changes(); -- Output: 4 (3 inserts + 1 update)

The typeof(X) function returns the data type of X. It can be used to check whether a value is an integer, text, blob, etc.

Example: Check the Type of an Order ID

Let’s say you’re working with an order ID, and you want to know its data type.

typeof({{order.id}})

Use Case in a Formula

• If {{order.id}} is an integer, typeof({{order.id}}) will return “integer”.
• If {{order.id}} is a string, it will return “text”.

• load_extension(X) function is used to load an SQLite extension from the specified file X.
• load_extension(X,Y) function is used to load a SQLite extension from the file X and specify an entry point Y.

Example: Load a Custom SQLite Extension

If you have a custom extension for SQLite that enhances its capabilities, you can load it as follows:

load_extension('path/to/custom_extension.so')

Example: Load an Extension with Specific Entry Point

If you have an extension that requires a specific entry point to be loaded, you can specify it as follows:

load_extension('path/to/custom_extension.so', 'extension_entry_point')

Use Case in a Formula

• If you have an extension that adds advanced mathematical functions, you can load it during your SQLite operations.
• This allows you to use the custom functionality within your queries after loading the extension.

The phpDate function formats a date string according to PHP’s date() function. This is useful for formatting dates in a specific way using the patterns recognized by PHP.

Example: Format a Date

If you want to format a timestamp into a more readable date format:

phpDate('{{order.timestamp}}','Y-m-d H:i:s')

Use Case in a Formula

• If {{order.timestamp}} is a Unix timestamp, the output would be the formatted date in the “YYYY-MM-DD HH:MM:SS” format, such as “2025-02-10 15:30:00”.

The date_format function formats a date string according to PHP’s date() conventions. It allows for customization of the date output format.

Example: Format a Date

For instance, if you want to format the date in MM/DD/YYYY format, you can use the following query:

date_format('{{order.date}}','%m/%d/%Y')

Use Case in a Formula

• If {{order.date}} is 2025-02-10, the output would be “02/10/2025”.
• date_format – Formats a given date according to PHP conventions.

The octet_length function returns the length of a string or data in bytes. This can be useful for checking the size of a string or blob before storing or processing it.

Example: Get the Byte Length of a String

octet_length('{{user.name}}')

Use Case in a Formula

• If {{user.name}} is “John”, the output would be 4 because the string “John” is 4 bytes in UTF-8 encoding.

• The unhex(X) function converts a hexadecimal string into a binary blob. This is useful when you need to store or process binary data that has been represented in hexadecimal format.
• The unhex(X,Y) function works similarly to unhex(X) but allows you to specify a delimiter Y to separate the hexadecimal string. This function is useful when you have a concatenated string of multiple hex values and want to convert them into binary data with a specific delimiter.

Returns NULL if:

• X or Y is NULL.
• X has an odd number of characters (hexadecimal digits come in pairs).
• X contains invalid hexadecimal characters (characters not in the range 0-9, A-F, or a-f).
• X contains characters that are not hexadecimal digits and are not included in the optional Y parameter.

Example: Convert Hexadecimal to Binary

unhex('68656c6c6f')

Use Case in a Formula

• The hexadecimal string ‘68656c6c6f’ represents the word “hello” in ASCII.
• The output of the above example would be the binary representation of the string “hello”.

The md5(X) function generates an MD5 hash of the input string X. MD5 is a widely-used hash function that produces a 128-bit hash value. It is commonly used for integrity checks or storing hashed representations of data (e.g., passwords).

Example: Generate MD5 Hash

md5('Wiresk')

Use Case in a Formula

• The string ‘Wiresk’ would be hashed to “a8e3c9b85319869b0d1084c8d7b835a2” using MD5.
• The output will be the MD5 hash of the string.

The sha1(X) function generates a SHA-1 hash of the input string X. SHA-1 is another cryptographic hash function that produces a 160-bit hash value. It is often used for verifying data integrity and ensuring authenticity.

Example: Generate SHA-1 Hash

sha1('Wiresk')

Use Case in a Formula

• The string ‘Wiresk’ would be hashed to “cd3d7f7a4f80d739f42b4965ba046f053b828e87” using SHA-1.
• The output will be the SHA-1 hash of the string.

The sha256(X) function generates a SHA-256 hash of the input string X. SHA-256 is part of the SHA-2 family of cryptographic hash functions and produces a 256-bit hash value. It is more secure than SHA-1 and commonly used for data integrity and digital signatures.

Example: Generate SHA-256 Hash

sha256('Wiresk')

Use Case in a Formula

• The string ‘Wiresk’ would be hashed to “0b61ed3654c7a1464cf9bdf49a6d75537206a914ce87927be7e6cc75c31aebfd” using SHA-256.
• The output will be the SHA-256 hash of the string.

The sha512(X) function generates a SHA-512 hash of the input string X. SHA-512 is a cryptographic hash function from the SHA-2 family, producing a 512-bit hash value. It’s widely used for its higher security compared to SHA-1 and SHA-256.

Example: Generate SHA-512 Hash

sha512('Wiresk')

Use Case in a Formula

• The string ‘Wiresk’ would be hashed to “2c74fd17edafd80e8447b0d46741ee243b7e5a473637ea64d43733fa7e39a1c0b346d8c3c4d5c619e7757c92f0e02b93e6978f6992408b4361b906d24d12e5b0” using SHA-512.
• The output will be the SHA-512 hash of the string.