Welcome To TechBrothersIT: Snowflake SQL Tutorial 64 - CONCAT, Double Pipe and LENGTH in Snowflake

Snowflake SQL · Episode 64

In this post we are going to cover CONCAT, the || operator and LENGTH in Snowflake.

CONCAT and || join pieces of text together into one string. LENGTH tells us how many characters a string has. Those are the two jobs we do more than any other when we are working with text.

We will join two columns, prove that the two spellings of the join really are the same, and then spend most of the post on the one thing that quietly ruins a concatenation: a single NULL. After that we will measure text, change its case, clean up a group key, and draw a bar chart out of nothing but a string function.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 64 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's create some sample data
Let's start by joining two columns together
What happens when one of them is NULL?
Now let's try to fix it
Now let's measure text with LENGTH
Now let's change case with UPPER, LOWER and INITCAP
Can case and whitespace ruin a GROUP BY?
Now let's try REVERSE and REPEAT
Let's put all of it on one row

Let's create some sample data

Level 7 is functions: strings, numbers, dates and conversion. It runs on two tables, and both of them come from the course setup script. Run it once.

Sample data — run the course setup once

-- CUSTOMERS and SALES_RAW both come from the setup script this course
-- uses throughout (00_SETUP_Sample_Data.sql). Run it once; every episode
-- reuses the same tables.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

-- The two tables this page lives in:
SELECT CUSTOMER_ID, CUSTOMER_NAME, CITY, COUNTRY, SEGMENT
  FROM CUSTOMERS ORDER BY CUSTOMER_ID;

SELECT ROW_ID, CUSTOMER_TXT, AMOUNT_TXT, DATE_TXT, REGION_TXT
  FROM SALES_RAW ORDER BY ROW_ID;

The first table is CUSTOMERS, which is clean. We will use it to show what each function does.

Query — the clean table

SELECT CUSTOMER_ID, CUSTOMER_NAME, CITY, COUNTRY, SEGMENT
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;

Result10 rows · 5 columns

CUSTOMER_ID	CUSTOMER_NAME	CITY	COUNTRY	SEGMENT
101	Northwind Traders	Charlotte	USA	Enterprise
102	Blue Ridge Supply	Asheville	USA	SMB
103	Kestrel Analytics	Toronto	Canada	Enterprise
104	Harbour Foods	Vancouver	Canada	SMB
105	Alpine Logistics	Zurich	NULL	Enterprise
106	Delta Print Co	Raleigh	USA	SMB
107	Meridian Health	Boston	USA	Enterprise
108	Copper Kettle Ltd	Manchester	UK	SMB
109	Vantage Media	London	UK	Enterprise
110	Sable Interiors	Austin	USA	SMB

Ten customers. The column to notice is COUNTRY, which is NULL for one of them. That single NULL is most of this post.

The second table is SALES_RAW, which is the opposite. It is text that came from somewhere else, with the mess left in. We will be living in this one for most of Level 7.

Query — the messy table

SELECT ROW_ID, CUSTOMER_TXT, AMOUNT_TXT, DATE_TXT, REGION_TXT
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 5 columns

ROW_ID	CUSTOMER_TXT	AMOUNT_TXT	DATE_TXT	REGION_TXT
1	Northwind Traders	$4,200.00	2023-01-12	us-east
2	blue ridge supply	890.50	01/28/2023	US-EAST
3	KESTREL ANALYTICS	$6,150	14-Feb-2023	ca-west
4	Harbour Foods	2,310.75	2023-03-09	CA-West
5	Alpine Logistics	N/A	2023-04-05	eu-central
6	Delta Print Co	$640.00	05/21/2023	us-east
7	Meridian Health	9,250.00	14-Aug-2023	US-East
8	copper kettle ltd	$1,080	2024-09-29	uk-north

Every column is a VARCHAR, including the ones that are obviously an amount and a date. The casing is inconsistent. The amounts carry currency symbols and thousands separators. The dates come in three different formats.

And some of the names have leading or trailing spaces we cannot see in a result grid. Finding those spaces is what LENGTH is for, and we will get to it.

Let's start by joining two columns together

CONCAT takes any number of arguments and joins them into one string. Separators are not implied. If we want a comma and a space between two values, we pass the comma and the space as an argument.

Query — CONCAT(CITY, ', ', COUNTRY)

SELECT CUSTOMER_ID, CITY, COUNTRY,
       CONCAT(CITY, ', ', COUNTRY) AS WITH_CONCAT
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;

Result10 rows · 4 columns

CUSTOMER_ID	CITY	COUNTRY	WITH_CONCAT
101	Charlotte	USA	Charlotte, USA
102	Asheville	USA	Asheville, USA
103	Toronto	Canada	Toronto, Canada
104	Vancouver	Canada	Vancouver, Canada
105	Zurich	NULL	NULL
106	Raleigh	USA	Raleigh, USA
107	Boston	USA	Boston, USA
108	Manchester	UK	Manchester, UK
109	London	UK	London, UK
110	Austin	USA	Austin, USA

The || operator does the same job with less typing, and it reads better in a long expression. Let's put them side by side.

Query — CONCAT against ||

SELECT CUSTOMER_ID,
       CONCAT(CITY, ', ', COUNTRY) AS WITH_CONCAT,
       CITY || ', ' || COUNTRY     AS WITH_PIPES
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;

Result10 rows · 3 columns

CUSTOMER_ID	WITH_CONCAT	WITH_PIPES
101	Charlotte, USA	Charlotte, USA
102	Asheville, USA	Asheville, USA
103	Toronto, Canada	Toronto, Canada
104	Vancouver, Canada	Vancouver, Canada
105	NULL	NULL
106	Raleigh, USA	Raleigh, USA
107	Boston, USA	Boston, USA
108	Manchester, UK	Manchester, UK
109	London, UK	London, UK
110	Austin, USA	Austin, USA

Identical, column for column. But “identical in this grid” is not a proof, and there is a cheap way to turn it into one. We can MINUS the second result from the first and see what is left over.

Query — CONCAT result MINUS || result

SELECT CUSTOMER_ID, CONCAT(CITY, ', ', COUNTRY) AS V FROM CUSTOMERS
MINUS
SELECT CUSTOMER_ID, CITY || ', ' || COUNTRY FROM CUSTOMERS;

Result0 rows · 2 columns

CUSTOMER_ID	V

Nothing is left over. Every row of one appears in the other, so the two spellings produced the same set of values.

Use whichever reads better in the expression you are writing. There is no third consideration.

Prove it, do not state it

An empty result is a real answer, and this is the pattern worth stealing. When you believe two expressions agree, MINUS one from the other and make the claim checkable instead of asserting it. Episode 35 covers MINUS in its own right.

What happens when one of them is NULL?

Let's go back to the CONCAT(CITY, ', ', COUNTRY) grid and look at customer 105. CITY is Zurich and COUNTRY is NULL.

WITH_CONCAT is not Zurich, and it is not Zurich, either. It is NULL. Here is that row on its own.

Query — the row that lost its label

SELECT CUSTOMER_ID, CUSTOMER_NAME, CITY, COUNTRY,
       CITY || ', ' || COUNTRY AS LABEL
FROM CUSTOMERS
WHERE COUNTRY IS NULL;

Result1 row · 5 columns

CUSTOMER_ID	CUSTOMER_NAME	CITY	COUNTRY	LABEL
105	Alpine Logistics	Zurich	NULL	NULL

Anything joined to a NULL gives us a NULL. Not the parts that were fine with a gap where the missing value was. The whole expression.

That is consistent with how NULL behaves in arithmetic. It is far more damaging here, because the thing we have lost is a label a human was going to read.

One row out of ten is easy to miss in a grid, so let's count it instead.

Query — counted rather than eyeballed

SELECT COUNT(*) AS CUSTOMERS,
       COUNT(CITY) AS HAVE_CITY,
       COUNT(COUNTRY) AS HAVE_COUNTRY,
       COUNT(CITY || ', ' || COUNTRY) AS LABEL_SURVIVED
FROM CUSTOMERS;

Result1 row · 4 columns

CUSTOMERS	HAVE_CITY	HAVE_COUNTRY	LABEL_SURVIVED
10	10	9	9

Ten customers. All ten have a city. Nine have a country. And nine labels survived.

That works because COUNT of an expression counts the rows where that expression is not NULL, which makes it a neat way to measure the damage.

The failure mode

Nothing warned us. No error, no warning, no partial value — one row in a report simply came back blank. On a dashboard with more rows than anyone reads, this is the kind of defect that survives for months, because an empty cell reads as missing data rather than as a bug in the expression that built it.

Now let's try to fix it

There are three obvious things to reach for, and only two of them work.

Let's put all three in one grid, on the NULL row and on a row that was fine, so we can see that the fixes do not damage the good rows.

Query — three attempts at the same fix

SELECT CUSTOMER_ID, CITY, COUNTRY,
       CITY || ', ' || COALESCE(COUNTRY, 'Unknown')    AS WITH_COALESCE,
       CONCAT_WS(', ', CITY, COUNTRY)                  AS WITH_CONCAT_WS,
       CITY || COALESCE(', ' || COUNTRY, '')           AS DROP_THE_SEPARATOR_TOO
FROM CUSTOMERS
WHERE COUNTRY IS NULL OR CUSTOMER_ID = 101
ORDER BY CUSTOMER_ID;

Result2 rows · 6 columns · scroll →

CUSTOMER_ID	CITY	COUNTRY	WITH_COALESCE	WITH_CONCAT_WS	DROP_THE_SEPARATOR_TOO
101	Charlotte	USA	Charlotte, USA	Charlotte, USA	Charlotte, USA
105	Zurich	NULL	Zurich, Unknown	NULL	Zurich

CITY || ', ' || COALESCE(COUNTRY, 'Unknown') — works. COALESCE replaces the NULL before the join ever sees it, and the row reads Zurich, Unknown. Use this when a stand-in value is the right answer.
CONCAT_WS(', ', CITY, COUNTRY) — does not work in Snowflake. It is NULL. More on this below.
CITY || COALESCE(', ' || COUNTRY, '') — works, and is the neat one. The separator is inside the COALESCE, so when COUNTRY is NULL the comma disappears with it and the row reads Zurich — no stand-in value, no trailing comma.

That third form is the one to remember. The trick is general. Put the separator in the same expression as the value it belongs to, so that a missing value takes its punctuation with it.

Why CONCAT_WS did not save us

CONCAT_WS is “concatenate with separator”. The first argument is the separator and the rest are the values. In several other databases it skips NULL arguments, which would make it exactly the right tool here.

A correction, left in

This post was written expecting CONCAT_WS to skip the NULL, and the captured result says it does not. In Snowflake a NULL argument makes the whole call NULL, exactly as || does. The three-fixes grid is the evidence, and it is on the page rather than quietly removed. Check this behaviour in your own account before you rely on it — that is the real lesson here, not the function.

It is worth seeing over the whole table rather than on one row, because CONCAT_WS is genuinely useful when nothing is NULL.

Query — CONCAT_WS over all ten customers

SELECT CUSTOMER_ID, CONCAT_WS(', ', CITY, COUNTRY, SEGMENT) AS LABEL
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;

Result10 rows · 2 columns

CUSTOMER_ID	LABEL
101	Charlotte, USA, Enterprise
102	Asheville, USA, SMB
103	Toronto, Canada, Enterprise
104	Vancouver, Canada, SMB
105	NULL
106	Raleigh, USA, SMB
107	Boston, USA, Enterprise
108	Manchester, UK, SMB
109	London, UK, Enterprise
110	Austin, USA, SMB

Nine clean three-part labels and one NULL, and the NULL is customer 105 again.

CONCAT_WS saves us typing the separator repeatedly. It does not save us from NULL.

Now let's measure text with LENGTH

LENGTH returns the number of characters. On clean data there is nothing to say about it.

Query — LENGTH on clean names

SELECT CUSTOMER_ID, CUSTOMER_NAME, LENGTH(CUSTOMER_NAME) AS CHARS
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;

Result10 rows · 3 columns

CUSTOMER_ID	CUSTOMER_NAME	CHARS
101	Northwind Traders	17
102	Blue Ridge Supply	17
103	Kestrel Analytics	17
104	Harbour Foods	13
105	Alpine Logistics	16
106	Delta Print Co	14
107	Meridian Health	15
108	Copper Kettle Ltd	17
109	Vantage Media	13
110	Sable Interiors	15

The longest customer names here are 17 characters, and four of the ten share that length. Unremarkable, which is the point, because the same function on the messy table is a diagnostic tool.

Let's run it there. The trick is wrapping each value in square brackets, which is the only reliable way to see whitespace in a result grid.

Query — LENGTH against LENGTH(TRIM(...))

SELECT ROW_ID, '[' || CUSTOMER_TXT || ']' AS AS_STORED,
       LENGTH(CUSTOMER_TXT) AS RAW_LEN,
       LENGTH(TRIM(CUSTOMER_TXT)) AS TRIMMED_LEN,
       LENGTH(CUSTOMER_TXT) - LENGTH(TRIM(CUSTOMER_TXT)) AS SPACES
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 5 columns

ROW_ID	AS_STORED	RAW_LEN	TRIMMED_LEN	SPACES
1	[ Northwind Traders ]	20	17	3
2	[blue ridge supply]	17	17	0
3	[KESTREL ANALYTICS]	17	17	0
4	[Harbour Foods ]	15	13	2
5	[Alpine Logistics]	16	16	0
6	[ Delta Print Co]	15	14	1
7	[Meridian Health]	15	15	0
8	[copper kettle ltd]	17	17	0

Row 1 is Northwind Traders with two spaces at the front and one at the back. Raw length 20, trimmed length 17, and the difference is the 3 in the SPACES column.

Three of the eight rows carry hidden whitespace, and none of them look any different from the others until we measure them.

LENGTH(x) - LENGTH(TRIM(x)) is a profiling query worth keeping. Run it on any text column you are about to join on, group by, or compare, and it tells you in one pass whether you have a whitespace problem.

`NULL` and the empty string are not the same thing

Let's measure both and see.

Query — LENGTH(NULL) against LENGTH('')

SELECT LENGTH(NULL) AS LEN_OF_NULL, LENGTH('') AS LEN_OF_EMPTY,
       COUNT(COUNTRY) AS NON_NULL_COUNTRIES, COUNT(LENGTH(COUNTRY)) AS NON_NULL_LENGTHS
FROM CUSTOMERS;

Result1 row · 4 columns

LEN_OF_NULL	LEN_OF_EMPTY	NON_NULL_COUNTRIES	NON_NULL_LENGTHS
NULL	0	9	9

LENGTH(NULL) is NULL. LENGTH('') is 0.

Those are two different answers to two different questions. “I do not know how long this is” against “it is empty”. Treating them as interchangeable is a real source of bugs, because LENGTH(x) = 0 is false for a NULL, not true.

NON_NULL_COUNTRIES and NON_NULL_LENGTHS make the same point from the data. Nine countries are not NULL, and nine lengths are not NULL. LENGTH passed the NULL straight through rather than turning it into a zero.

Now let's change case with UPPER, LOWER and INITCAP

Three case functions, and they divide cleanly by purpose. UPPER and LOWER are for comparing and grouping. INITCAP is for displaying.

Let's run all three on the messy names.

Query — all three on the messy names

SELECT ROW_ID, CUSTOMER_TXT,
       UPPER(CUSTOMER_TXT)   AS UPPERED,
       LOWER(CUSTOMER_TXT)   AS LOWERED,
       INITCAP(CUSTOMER_TXT) AS INITCAPPED
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 5 columns

ROW_ID	CUSTOMER_TXT	UPPERED	LOWERED	INITCAPPED
1	Northwind Traders	NORTHWIND TRADERS	northwind traders	Northwind Traders
2	blue ridge supply	BLUE RIDGE SUPPLY	blue ridge supply	Blue Ridge Supply
3	KESTREL ANALYTICS	KESTREL ANALYTICS	kestrel analytics	Kestrel Analytics
4	Harbour Foods	HARBOUR FOODS	harbour foods	Harbour Foods
5	Alpine Logistics	ALPINE LOGISTICS	alpine logistics	Alpine Logistics
6	Delta Print Co	DELTA PRINT CO	delta print co	Delta Print Co
7	Meridian Health	MERIDIAN HEALTH	meridian health	Meridian Health
8	copper kettle ltd	COPPER KETTLE LTD	copper kettle ltd	Copper Kettle Ltd

Notice that none of the three touched the leading and trailing spaces. Case functions change case. They are not a cleaning step on their own.

Case is not reversible

It is tempting to treat UPPER and LOWER as inverses of each other. They are not. The original casing is information, and UPPER destroys it.

Let's round-trip every value and compare it with what went in.

Query — LOWER(UPPER(x)) compared with x

SELECT ROW_ID, CUSTOMER_TXT,
       LOWER(UPPER(CUSTOMER_TXT)) AS UPPER_THEN_LOWER,
       CUSTOMER_TXT = LOWER(UPPER(CUSTOMER_TXT)) AS SAME_AS_ORIGINAL
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 4 columns

ROW_ID	CUSTOMER_TXT	UPPER_THEN_LOWER	SAME_AS_ORIGINAL
1	Northwind Traders	northwind traders	FALSE
2	blue ridge supply	blue ridge supply	TRUE
3	KESTREL ANALYTICS	kestrel analytics	FALSE
4	Harbour Foods	harbour foods	FALSE
5	Alpine Logistics	alpine logistics	FALSE
6	Delta Print Co	delta print co	FALSE
7	Meridian Health	meridian health	FALSE
8	copper kettle ltd	copper kettle ltd	TRUE

Only two of the eight rows come back equal to what went in, and both of them are values that were already lower case to begin with. There was nothing for the round trip to lose.

Case-fold for comparison, never for storage, unless you are willing to throw the original away.

INITCAP has opinions about punctuation

INITCAP capitalises the first letter of each word and lower-cases the rest. That is why KESTREL ANALYTICS becomes Kestrel Analytics rather than staying shouted.

The interesting part is what it considers a word boundary. Let's feed it an apostrophe, a hyphen and a digit.

Query — INITCAP against an apostrophe, a hyphen and a digit

SELECT INITCAP('daniel o''connor')      AS APOSTROPHE,
       INITCAP('KESTREL ANALYTICS')     AS ALL_CAPS,
       INITCAP('blue ridge supply')     AS ALL_LOWER,
       INITCAP('mcdonald-smith')        AS HYPHEN,
       INITCAP('3m corporation')        AS STARTS_WITH_A_DIGIT;

Result1 row · 5 columns

APOSTROPHE	ALL_CAPS	ALL_LOWER	HYPHEN	STARTS_WITH_A_DIGIT
Daniel O'connor	Kestrel Analytics	Blue Ridge Supply	Mcdonald-Smith	3m Corporation

Input	Output	What it tells you
`daniel o'connor`	`Daniel O'connor`	An apostrophe is not a word boundary, so the `c` stays lower case
`mcdonald-smith`	`Mcdonald-Smith`	A hyphen is a word boundary, so the `S` is capitalised
`3m corporation`	`3m Corporation`	A digit is not a word boundary, so the `m` stays lower case

Three different rules in one row, and no amount of reasoning from first principles will tell us which is which.

If you are using INITCAP on names, test it against your actual names. Apostrophes and hyphens are common in exactly the data people most want capitalised nicely.

Can case and whitespace ruin a GROUP BY?

Everything so far is a function reference until we put it next to a GROUP BY. Let's group the messy table by its customer text exactly as stored.

Query — GROUP BY the raw text

SELECT CUSTOMER_TXT, COUNT(*) AS N
FROM SALES_RAW
GROUP BY CUSTOMER_TXT
ORDER BY CUSTOMER_TXT;

Result8 rows · 2 columns

CUSTOMER_TXT	N
Northwind Traders	1
Delta Print Co	1
Alpine Logistics	1
Harbour Foods	1
KESTREL ANALYTICS	1
Meridian Health	1
blue ridge supply	1
copper kettle ltd	1

Eight groups from eight rows, every one with a count of 1. Every one of those is a different customer, so this is a faithful answer.

But notice the sort order. It puts Northwind Traders and Delta Print Co first, ahead of everything beginning with a letter, and it puts the lower-case names last. That is whitespace and case deciding our output order.

Now let's group by the cleaned value instead, with UPPER(TRIM(...)), which is the standard pair.

Query — GROUP BY UPPER(TRIM(...))

SELECT UPPER(TRIM(CUSTOMER_TXT)) AS CLEANED, COUNT(*) AS N
FROM SALES_RAW
GROUP BY UPPER(TRIM(CUSTOMER_TXT))
ORDER BY CLEANED;

Result8 rows · 2 columns

CLEANED	N
ALPINE LOGISTICS	1
BLUE RIDGE SUPPLY	1
COPPER KETTLE LTD	1
DELTA PRINT CO	1
HARBOUR FOODS	1
KESTREL ANALYTICS	1
MERIDIAN HEALTH	1
NORTHWIND TRADERS	1

Still eight groups, and this post is not going to pretend otherwise. Cleaning the key did not collapse anything, because there are no duplicates hiding in these eight rows. Every value really is a different customer.

Being straight about this one

What matters here is the shape of the fix, not a smaller number. On real data, Harbour Foods and harbour foods are the same customer and an untreated GROUP BY reports them as two, which is where double-counted revenue comes from. The cleaned GROUP BY grid shows that the keys are now normalised and sorted sensibly. It does not show that cleaning found duplicates — it did not, and claiming it did would be the easiest lie on this page.

UPPER(TRIM(x)) is the join and group key to reach for whenever text has been typed by a human or exported by another system. It costs nothing and removes the two most common reasons two identical values refuse to match.

Now let's try REVERSE and REPEAT

Two short ones to finish. REVERSE does exactly what it says.

Query — REVERSE

SELECT CUSTOMER_ID, CUSTOMER_NAME, REVERSE(CUSTOMER_NAME) AS BACKWARDS
FROM CUSTOMERS
WHERE CUSTOMER_ID <= 103
ORDER BY CUSTOMER_ID;

Result3 rows · 3 columns

CUSTOMER_ID	CUSTOMER_NAME	BACKWARDS
101	Northwind Traders	sredarT dniwhtroN
102	Blue Ridge Supply	ylppuS egdiR eulB
103	Kestrel Analytics	scitylanA lertseK

Unlike case folding, this one loses nothing. It is its own inverse, and that is checkable rather than obvious, so let's check it.

Query — REVERSE(REVERSE(x)) = x, counted

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(REVERSE(REVERSE(CUSTOMER_NAME)) = CUSTOMER_NAME) AS ROUND_TRIPPED
FROM CUSTOMERS;

Result1 row · 2 columns

ROWS_TOTAL	ROUND_TRIPPED
10	10

Ten rows in, ten round-tripped. Compare that with the LOWER(UPPER(x)) grid, where only two did. Reversing is information-preserving. Case folding is not.

`REPEAT`, and a bar chart in a result grid

REPEAT takes a string and a count. Its most entertaining use is drawing a chart in text, straight out of the query. Let's draw one # per character of the customer name.

Query — REPEAT('#', LENGTH(...))

SELECT CUSTOMER_ID, CUSTOMER_NAME, SEGMENT,
       REPEAT('#', LENGTH(CUSTOMER_NAME)) AS NAME_LENGTH_BAR
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;

Result10 rows · 4 columns

CUSTOMER_ID	CUSTOMER_NAME	SEGMENT	NAME_LENGTH_BAR
101	Northwind Traders	Enterprise	#################
102	Blue Ridge Supply	SMB	#################
103	Kestrel Analytics	Enterprise	#################
104	Harbour Foods	SMB	#############
105	Alpine Logistics	Enterprise	################
106	Delta Print Co	SMB	##############
107	Meridian Health	Enterprise	###############
108	Copper Kettle Ltd	SMB	#################
109	Vantage Media	Enterprise	#############
110	Sable Interiors	SMB	###############

Each bar is as long as the name beside it, so the CHARS column we measured with LENGTH is now something we can read at a glance.

The same trick works on any number we can scale into a sensible count, and it needs no chart library, no export, and no second tool.

The edge cases are worth knowing before they reach a report. Square brackets again, because an empty string is otherwise invisible.

Query — REPEAT at 3, 1, 0 and -2

SELECT '[' || REPEAT('ab', 3) || ']'  AS THREE_TIMES,
       '[' || REPEAT('ab', 1) || ']'  AS ONCE,
       '[' || REPEAT('ab', 0) || ']'  AS ZERO_TIMES,
       '[' || REPEAT('ab', -2) || ']' AS NEGATIVE;

Result1 row · 4 columns

THREE_TIMES	ONCE	ZERO_TIMES	NEGATIVE
[ababab]	[ab]	[]	[]

Negative counts fail quietly

A count of 0 gives an empty string, which is what anyone would expect. A negative count also gives an empty string — not an error. So a bar chart fed a negative value does not fail loudly; it silently draws nothing, and an empty cell in a report looks exactly like a zero. Guard the count with GREATEST(n, 0) if you care about telling those two apart.

Let's put all of it on one row

One query with everything from this post stacked up on a single real value: the raw text, its length, the trimmed version, the tidied version, and the length after tidying.

Query — raw, measured, trimmed, tidied, measured again

SELECT CUSTOMER_TXT AS RAW,
       LENGTH(CUSTOMER_TXT) AS RAW_LEN,
       TRIM(CUSTOMER_TXT) AS TRIMMED,
       INITCAP(TRIM(CUSTOMER_TXT)) AS TIDIED,
       LENGTH(INITCAP(TRIM(CUSTOMER_TXT))) AS TIDIED_LEN
FROM SALES_RAW
WHERE ROW_ID = 1;

Result1 row · 5 columns

RAW	RAW_LEN	TRIMMED	TIDIED	TIDIED_LEN
Northwind Traders	20	Northwind Traders	Northwind Traders	17

20 characters in, 17 out, and a value we would be happy to put in front of someone.

That is the whole workflow of Level 7 in one row. Measure first, clean second, and measure again to prove the clean worked.

TRIM has been doing quiet work in several queries on this page without being explained. That is the next post's job, along with its one-sided relatives and the padding functions.

Key points

CONCAT and || are the same function with two spellings, proved here with a MINUS that returns nothing. Use whichever reads better.
A single NULL anywhere in a concatenation makes the whole result NULL — not a partial string with a gap. Here it cost one customer their entire label, with no error.
COALESCE is the fix, and the neat form puts the separator inside it: CITY || COALESCE(', ' || COUNTRY, '') drops the comma along with the missing value.
CONCAT_WS does not skip NULLs in Snowflake, even though it does in several other databases. This post expected otherwise and the captured grid says no.
LENGTH counts characters you cannot see in a result grid. LENGTH(x) - LENGTH(TRIM(x)) is a one-line whitespace profiler for any text column.
LENGTH(NULL) is NULL and LENGTH('') is 0. They are different answers and LENGTH(x) = 0 is not true for a NULL.
UPPER and LOWER are for comparing and grouping; INITCAP is for display. LOWER(UPPER(x)) does not give you back x — only two of eight rows here survived the round trip.
INITCAP treats a hyphen as a word boundary but an apostrophe and a digit as not. Test it on your own names before trusting it.
UPPER(TRIM(x)) is the group and join key for human-typed text. It changed no counts on this clean-ish sample and it is still the right habit.
REVERSE is its own inverse. REPEAT with a count of zero or a negative count returns an empty string rather than an error.

Next in this series

Episode 65 — Strings 2. TRIM, LTRIM and RTRIM for removing the whitespace we just found, what happens when you give them characters other than a space, and LPAD and RPAD for fixing a value to a width.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including the CONCAT_WS result that contradicts what the episode expected. The CUSTOMERS and SALES_RAW data is invented demo data created by the course setup script. No performance or benchmark claims are made anywhere in this series. Function behaviour follows the Snowflake string function documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 64 - CONCAT, Double Pipe and LENGTH in Snowflake - Strings 1

Let's create some sample data

Let's start by joining two columns together

What happens when one of them is NULL?

Now let's try to fix it

Why CONCAT_WS did not save us

Now let's measure text with LENGTH

`NULL` and the empty string are not the same thing

Now let's change case with UPPER, LOWER and INITCAP

Case is not reversible

INITCAP has opinions about punctuation

Can case and whitespace ruin a GROUP BY?

Now let's try REVERSE and REPEAT

`REPEAT`, and a bar chart in a result grid

Let's put all of it on one row

Key points

No comments:

Post a Comment

Label

Snowflake SQL Tutorial 64 - CONCAT, Double Pipe and LENGTH in Snowflake - Strings 1

Let's create some sample data

Let's start by joining two columns together

What happens when one of them is NULL?

Now let's try to fix it

Why CONCAT_WS did not save us

Now let's measure text with LENGTH

NULL and the empty string are not the same thing

Now let's change case with UPPER, LOWER and INITCAP

Case is not reversible

INITCAP has opinions about punctuation

Can case and whitespace ruin a GROUP BY?

Now let's try REVERSE and REPEAT

REPEAT, and a bar chart in a result grid

Let's put all of it on one row

Key points

No comments:

Post a Comment

`NULL` and the empty string are not the same thing

`REPEAT`, and a bar chart in a result grid