Learning Snowflake SQL and Scripting

Chapter 6. Data Generation, Conversion, and Manipulation

In Chapter 5, you learned about the different data types available in Snowflake, including numeric, temporal, and character. This chapter will explore Snowflake’s many built-in functions used to generate, convert, and manipulate values for these data types.

Working with Character Data

Snowflake includes a wide array of functions used for handling character data, whether you need to concatenate strings, search for substrings, change to upper- or lowercase, or do just about anything else.

String Generation and Manipulation

Creating a string literal is a simple matter of enclosing your string in single quotes:

PUBLIC>select 'here is my string';
+---------------------+
| 'HERE IS MY STRING' |
|---------------------|
| here is my string   |
+---------------------+

If you have two strings that you want to concatenate, you can choose between using the || operator, or using the built-in concat() function:

PUBLIC>select 'string 1' || ' and ' || 'string2';
+------------------------------------
| 'STRING 1' || ' AND ' || 'STRING2' |
|------------------------------------|
| string 1 and string2               |
+------------------------------------+

PUBLIC>select concat('string1',' and ','string2');
+-------------------------------------+
| CONCAT('STRING1',' AND ','STRING2') |
|-------------------------------------|
| string1 and string2                 |
+-------------------------------------+

If you need to include a character ...