Manipulating text data

1. Manipulating text data

We've laid the groundwork! Now, let's dig into the tools Snowflake offers to manipulate data, starting with text data.

2. Finding the LENGTH of a string

We can find the number of characters in a string of text using the `LENGTH` function. `LENGTH` takes a column or value, and returns the number of characters, including spaces! In this example, `song_name` is passed to `LENGTH`, and the number of characters in each song is returned. `LENGTH` can be used with values of type `VARCHAR`, `TEXT`, `STRING`, and all their equivalents. If we'd like to save keystrokes, we could use `LEN`; it's synonymous with `LENGTH`.

3. TRIM

The `TRIM` function removes leading and trailing characters from text values, most commonly, spaces. `TRIM` takes two arguments. The first is the column or value to be trimmed. The second argument is optional; if provided this will be the sequence of characters that are trimmed from the beginning or end. If omitted, it will default to a space. `TRIM` is case-insensitive, and has counterparts "left TRIM" and "right TRIM" to only remove leading or trailing characters.

4. TRIM

Here, we're using `TRIM` to remove `(Remastered)` from `song_long_name`. First, we'll pass `song_long_name` to `TRIM`, followed by `(Remastered)`, in parentheses. The result is shown in `trimmed_song_name`.

5. SPLIT

Another common operation is "splitting" text. The `SPLIT` function chunks text into an array of values delimited by some separator. The first argument passed to `SPLIT` is the column or value to split. The second is the separator to split by. The result is a Snowflake `ARRAY`. In a second, we'll see how we can use bracket-notation to retrieve specific values. Using `SPLIT` would allow us to turn this single string of subjects into an `ARRAY` by "splitting" on commas.

6. SPLIT

Here, we're splitting the text storing song collaborators into a `ARRAY`. This is done by passing the `collaborators` field to `SPLIT`, followed by a comma in quotes. We can also retrieve specific values from the `ARRAY`. To retrieve the `primary_artist`, we add a bracket-zero-bracket after the call to split. This returns the first value in split text.

7. CONCAT

Last up, `CONCAT`. Using `CONCAT`, we can join two or more text values together into a single string. To build a concatenated string, we'll pass an arbitrary number of text values to `CONCAT`, separated by commas. Pro tip; if we'd like spaces between values, we'll need to specify them, as shown here. `CONCAT` is useful for tasks like combining first and last names, or building street addresses.

8. CONCAT

`CONCAT` is the perfect tool for writing a song description. We're passing three text values to `CONCAT`; the `song_name`, 'is written by', and `artist_name`. For the song Night Moves, this reads as "Night Moves is written by Bob Seger".

9. Let's practice!

Now, it's your turn to manipulate text data!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.