SQLite Series: Working with Strings and Texts


#1

SQLite Series Part I: Functions that actually let you work with strings and texts!

SQLite is the most used database engine in the world but why is this even relevant? SQLite actually powers our data pipeline which enables you to do cool nifty things in our custom formulas! In this series, we will explore some of the most useful functions you can utilize in the pipeline and show off your SQL skills!

Part 1 uses SQLite Core Functions. Other parts of the series will cover Date & Time functions, aggregate functions, and JSON functions. Core functions can be used with string and numeric data types and all the functions can be found here: SQLite Core Function. There are a lot of them but we’re going to cover the some of the ones you can use to work with strings/texts.


LENGTH(X)
When to use: When you need to know how many characters are in the string/text.
Example:

length("column name")


LOWER(X) / UPPER(X)
When to use: When you need to convert your string/text to all lowercase / all uppercase.
Example:

lower("column name")
upper("column name")


REPLACE(X,Y,Z)
When to use: When you need to replace a character/string in your string/text with another character/string.
How to use:

  • X represents the string/text that contains the character/string you want to replace

  • Y represents the character/string you want to replace

  • Z represents the character/string that is used to replace Y
    Example:

    replace(“column name”, ‘replace_this’, ‘with_this’)

In this example, we’re replacing the space in between the text (’ ‘) with dashes(’-’):


INSTR(X,Y)
When to use: When you need to find the position of a character in a string/text.
How to use:

  • X represents the string/text that contains the character you want to obtain the position of
  • Y represent the character that you want to obtain the position of

Example:

instr("column name", 'character')

In this example, we want to know where the first dash(’-’) is located in the slug so we can later obtain the company name from the slug:


SUBSTR(X,Y,Z)
When to use: When you need to obtain a substring of the string/text you’re working with
How to use:

  • X represents the string/text 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, so you may omit this argument) represents the ending position of substring
    Example:

    substr(“column name”, ‘start_position’, ‘end_position’)

Continuing the example from the INSTR(), 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 right before the first dash:

*the position of the first dash was obtained using the INSTR() function



This was just a portion of all the functions you can use through SQLite! As we go through the series, we’ll explore more functions you can utilize in your charts!