Strings in SQLite

Data Pipeline

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 mant 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’)

Here we are replacing the space in between the text (‘ ‘) with dashes(‘-’):

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 you want to obtain the position of.
  • Y represents the character that you want to obtain the position of.

Example

instr(“column name”, ‘character’)

Here we want to know where the first dash (‘-’) is located in the slug 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 ending position of substring.

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


Related Data Pipeline Help Articles

See more