Documentation Data Pipeline FAQs

SQLite string functions in Data Explorer

If you joined after January 6, 2020, you might be using our new Visual SQL interface, and this content may not look familiar to you. Check out our SQLite string functions in Visual SQL for more relevant content.

SQLite is the most used database engine in the world and it powers Chartio’s Data Pipeline. There are a lot of core functions that can be used with string and numeric data types, but this will cover some of the ones that work with strings (text).

LENGTH(X)

When to use

When you need to know how many characters are in the string.

Example

length("Column Name")

Use length to find out how many characters are in a string

LOWER(X) / UPPER(X)

When to use

When you need to convert your string to all lowercase or all uppercase letters.

Example

lower("Column Name")

upper("Column Name")

Convert a string to all lowercase or all uppercase

REPLACE(X, Y, Z)

When to use

When you need to replace a character or string in your string with another character or string.

How to use

X represents the string that contains the character or string you want to replace.
Y represents the character or string you want to replace.
Z represents the character or string that is used to replace Y.

Example

replace("Column Name",'replace_this','with_this')

In the example below, we are replacing the space in between the text (‘ ‘) with dashes(‘-‘) in the slug column:

Replace a character in a string with another character

INSTR(X, Y)

When to use

When you need to find the position of a character in a string.

How to use

X represents the string that contains the character to which you want to obtain the position.
Y represents the character to which you want to obtain the position.

Example

instr("Column Name",'Character')

Here, we want to know where the first dash (‘-‘) is located in the slug column so we can later obtain the company name from the slug.

Find the position of a character in a string

SUBSTR(X, Y, Z)

When to use

When you need to obtain a substring of the string you’re working with.

How to use

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 one).
Z (optional, so you may omit this argument) represents the number of characters in the resulting string.

Example

substr("Column Name",'start_position','end_position')

Here we want to obtain the company name from the slug since the slug contains the company name. We can do this by taking a substring of the slug from the first character to the right before the first dash (Note: The position of the first dash was obtained using the INSTR() function).

Obtain a substring from a string

Example 2

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

In an Edit Column step, select the custom formula option and using the function substr, you can enter:

'('||substr("Phone",0,4)||')'||' '||substr("Phone",4,3)||'-'||substr("Phone",7,5)

You would replace “Phone” with the name of the column you are editing.