SQLite string functions

Atlassian Analytics uses SQLite to power the non-query Visual SQL steps. There are many core functions that you can use for strings and numeric values, but here are some of the common ones you can use for strings:

LENGTH(X)

LENGTH("column name")

Returns the number of characters in the string

LOWER(X)

LOWER("column name")

Converts the string to all lowercase letters

UPPER(X)

UPPER("column name")

Converts the string to all uppercase letters

REPLACE(X, Y, Z)

REPLACE("column name", 'replace_this', 'with_this')

Replaces a character or substring in your string with another character or string

• X represents the string that contains the character or string you want to replace.

• Y represents the character or substring you want to replace.

• Z represents the character or string that is used to replace Y.

INSTR(X, Y)

Finds the position of a character in a string

• X represents the string that contains the character whose position you want to obtain.

• Y represents the character whose position you want to obtain.

If the character appears multiple times in the string, the function returns the position of its first occurrence.

SUBSTR(X, Y, Z)

SUBSTR("column name",'start_position','length')

Obtains a substring of the string you’re working with

• X represents the string you want to obtain a substring from.

• Y represents the starting position to obtain the substring (the first character position in the string is always 1).

• Z (optional; can be omitted) represents the number of characters in the resulting string.

Use SUBSTR() to format a phone number

If you have a phone number column in the format 5556667777, but you want the format (555) 666-7777, you can format it using the SUBSTR() function.

Use the following SUBSTR() function in a custom formula:

Replace Phone with the name of the column containing the phone number.

Use SUBSTR() to get the domain name from an email

Here’s a more complicated example. Let’s say we want to pull the domain name from each user’s company email. We know emails follow this general format: myname@mycompany.com

To make the final SUBSTR() function a bit cleaner, let’s get two pieces of information using the INSTR() function:

• The position of the @ in each email (We’ll name this column Position of @):

  1 INSTR("Email", '@')

• The position of the .com in each email (We’ll name this column Position of .com):

  1 INSTR("Email", '@')

Now let’s figure out how to get the domain from the email. Remember that the SUBSTR() function has three parameters: X, Y, and Z.

• Our X value is the column containing the email addresses, which is “Email”—that one’s easy.

• Our Y value needs to be the position after the @ position:
  "Position of @"+1

• Our Z value needs to be the length of the domain, which we can get by subtracting our Y value from the position before the .com (in other words, (Z-1)-Y):
  ("Position of .com"-1)-"Position of @"

This is our final SUBSTR() formula:

Took a bit of effort, but we’re able to successfully get the domain names from each user’s email!