Scribd
Upload
11 views1 page

3.+functions+ +String+Functions

The document provides SQL examples for various string and binary manipulation functions in Snowflake, including compression, concatenation, case conversion, and substring extraction. It highlights the use of specific functions like COMPRESS, CONCAT, UPPER, LOWER, SUBSTR, and SUBSTRING with sample queries on the CUSTOMER table. The document also mentions supported compression methods such as SNAPPY, ZLIB, ZSTD, and BZ2.

Uploaded by

tokixit492
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
11 views1 page

3.+functions+ +String+Functions

The document provides SQL examples for various string and binary manipulation functions in Snowflake, including compression, concatenation, case conversion, and substring extraction. It highlights the use of specific functions like COMPRESS, CONCAT, UPPER, LOWER, SUBSTR, and SUBSTRING with sample queries on the CUSTOMER table. The document also mentions supported compression methods such as SNAPPY, ZLIB, ZSTD, and BZ2.

Uploaded by

tokixit492
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

-- COMPRESS: Compresses the input string or binary value with a compression method.

-- outputs binary value/hexadecimal characters for readability.


/* snowflake supported compression methods:
SNAPPY
ZLIB
ZSTD
BZ2
*/
select *, compress(c_mktsegment, 'snappy') as new_mktsegment from
"SNOWFLAKE_SAMPLE_DATA"."TPCH_SF001"."CUSTOMER";

-- CONCAT: Concatenates one or more strings, or concatenates one or more binary


values.
-- If any of the values is null, the result is also null.
-- The || operator provides alternative syntax for CONCAT and requires at least two
arguments.
select *, concat(c_name, ' - ', c_mktsegment) as new_identifier from
"SNOWFLAKE_SAMPLE_DATA"."TPCH_SF001"."CUSTOMER";
select concat('Kashish ', 'Gakkar');

-- UPPER: Converts the string into UPPERCASE


-- LOWER: Converts the string into LOWERCASE
select *,
upper(c_comment) as uppercase_comment,
lower(c_mktsegment) as lowercase_segment
from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF001"."CUSTOMER";

-- SUBSTR, SUBSTRING: Returns the portion of the string or binary value from
original string. Length is optional parameter.
-- SUBSTR(string, start_pos, length) or SUBSTRING(string, start_pos, length)
select *,
substr(c_mktsegment, 2, 5) as short_segment ,
substring(c_mktsegment, 2, 4) as same_short_segment
from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF001"."CUSTOMER" limit 10;

select *,
substr(c_mktsegment, 2) as short_segment ,
substring(c_mktsegment, 2, 4) as same_short_segment
from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF001"."CUSTOMER" limit 10;

select *,
substr(c_mktsegment, 2) as short_segment ,
substring(c_mktsegment, -4, 2) as same_short_segment
from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF001"."CUSTOMER" limit 10;

You might also like