Snowflake SQL Tutorial 65 - TRIM, LTRIM, RTRIM, LPAD, RPAD in Snowflake - Strings 2

Snowflake SQL · Episode 65

In this post we are going to cover TRIM, LTRIM, RTRIM, LPAD and RPAD in Snowflake.

The trim functions take characters off the ends of a string. The pad functions add characters until the string reaches a width we ask for. Between them they do most of the tidying up we need before text is fit to group on or show to somebody.

Episode 64 used LENGTH to prove there are spaces in this table we cannot see in a result grid. We will start by removing them. Then we will give TRIM our own characters to remove and find out that the second argument is a set of characters rather than a substring. After that we will clean up a grouping key, pad some values to a fixed width, and see what padding does when the width we ask for is too small.

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

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

Let's create some sample data

Everything on this page runs against SALES_RAW, the deliberately messy text table from Episode 64, plus CUSTOMERS for the padding examples. Both come from the course setup script, so run it once.

Sample data — run the course setup once
-- SALES_RAW and CUSTOMERS 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;

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

Let's look at the raw text.

Query — the raw text
SELECT ROW_ID, CUSTOMER_TXT, AMOUNT_TXT, REGION_TXT
FROM SALES_RAW
ORDER BY ROW_ID;
Result8 rows · 4 columns
ROW_IDCUSTOMER_TXTAMOUNT_TXTREGION_TXT
1 Northwind Traders $4,200.00us-east
2blue ridge supply890.50US-EAST
3KESTREL ANALYTICS$6,150ca-west
4Harbour Foods 2,310.75CA-West
5Alpine LogisticsN/Aeu-central
6 Delta Print Co$640.00us-east
7Meridian Health9,250.00US-East
8copper kettle ltd$1,080uk-north

Nothing in that grid looks wrong, which is the problem. Let's wrap each value in square brackets and measure it.

Query — brackets and LENGTH
SELECT ROW_ID,
       '[' || CUSTOMER_TXT || ']' AS AS_STORED,
       LENGTH(CUSTOMER_TXT) AS LEN
FROM SALES_RAW
ORDER BY ROW_ID;
Result8 rows · 3 columns
ROW_IDAS_STOREDLEN
1[ Northwind Traders ]20
2[blue ridge supply]17
3[KESTREL ANALYTICS]17
4[Harbour Foods ]15
5[Alpine Logistics]16
6[ Delta Print Co]15
7[Meridian Health]15
8[copper kettle ltd]17

Now we can see it. Three rows out of eight carry whitespace at an end.

  • Row 1 — two spaces at the front and one at the back, which is why its length is 20 where the name itself is 17.
  • Row 4 — two spaces at the back.
  • Row 6 — one space at the front.

The other five rows are clean.

Brackets around a value are the cheapest debugging trick in SQL. A result grid will not show us whitespace, and neither will most client tools, so we make it visible before we go looking for a problem somewhere else.

Let's start with TRIM, LTRIM and RTRIM

Three functions, one job. They differ only in which end they work on. Let's put all three in one grid alongside the stored value.

Query — LTRIM, RTRIM and TRIM side by side
SELECT ROW_ID,
       '[' || CUSTOMER_TXT || ']'        AS AS_STORED,
       '[' || LTRIM(CUSTOMER_TXT) || ']' AS LEFT_ONLY,
       '[' || RTRIM(CUSTOMER_TXT) || ']' AS RIGHT_ONLY,
       '[' || TRIM(CUSTOMER_TXT) || ']'  AS BOTH_ENDS
FROM SALES_RAW
ORDER BY ROW_ID;
Result8 rows · 5 columns
ROW_IDAS_STOREDLEFT_ONLYRIGHT_ONLYBOTH_ENDS
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]
FunctionRemoves fromRow 1 result
LTRIM(x)the left only[Northwind Traders ] — the trailing space survives
RTRIM(x)the right only[ Northwind Traders] — the leading spaces survive
TRIM(x)both ends[Northwind Traders]

Plain TRIM is the one we will reach for most of the time. The one-sided pair matters when the whitespace carries meaning on one side. Fixed-width extracts, indentation we want to keep, that sort of thing.

Rather than pointing at rows, let's count them.

Query — the same thing, counted
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(CUSTOMER_TXT <> TRIM(CUSTOMER_TXT))  AS HAD_OUTER_SPACES,
       COUNT_IF(CUSTOMER_TXT <> LTRIM(CUSTOMER_TXT)) AS HAD_LEADING,
       COUNT_IF(CUSTOMER_TXT <> RTRIM(CUSTOMER_TXT)) AS HAD_TRAILING
FROM SALES_RAW;
Result1 row · 4 columns
ROWS_TOTALHAD_OUTER_SPACESHAD_LEADINGHAD_TRAILING
8322

Eight rows. Three had whitespace at an end, two had it at the front, and two had it at the back.

Two plus two is four, and only three rows are affected. That is because row 1 is in both of the narrower counts. Worth noticing before we build a report on top of two overlapping conditions and add them up.

A profiling query worth keeping

COUNT_IF(x <> TRIM(x)) is the shape of query to keep. It tells you how many rows a cleaning step would change, which is exactly the number you want before you run an UPDATE rather than after.

Can TRIM reach the middle of a value?

This is the thing people expect TRIM to do and it does not. Let's trim every row and then count the spaces still sitting inside each value.

Query — what is left inside after trimming
SELECT ROW_ID, '[' || TRIM(CUSTOMER_TXT) || ']' AS TRIMMED,
       LENGTH(TRIM(CUSTOMER_TXT)) AS TRIMMED_LEN,
       REGEXP_COUNT(TRIM(CUSTOMER_TXT), ' ') AS SPACES_LEFT_INSIDE
FROM SALES_RAW
ORDER BY ROW_ID;
Result8 rows · 4 columns
ROW_IDTRIMMEDTRIMMED_LENSPACES_LEFT_INSIDE
1[Northwind Traders]171
2[blue ridge supply]172
3[KESTREL ANALYTICS]171
4[Harbour Foods]131
5[Alpine Logistics]161
6[Delta Print Co]142
7[Meridian Health]151
8[copper kettle ltd]172

SPACES_LEFT_INSIDE is not zero anywhere. Every name on this page is at least two words, so every row still has an internal space, and the three-word names have two of them.

And that is correct behaviour, not a shortcoming. The space between Northwind and Traders is part of the name. A function that removed it would be useless for cleaning text.

The limit, stated plainly

TRIM cleans the edges of a value. It is not a general space remover. If what you actually want is “collapse runs of spaces” or “remove every space”, you want REPLACE or a regular expression, and both get their own posts later in this level.

Now let's give TRIM our own characters to remove

All three trim functions take an optional second argument: the characters to remove instead of whitespace.

This is where people go wrong, so it is worth being precise. TRIM(x, '$,') does not look for the text $,. It removes any of the characters $ and ,.

The amount column is a good target. There are currency symbols on some rows, thousands separators on others, and one row that is not a number at all.

Query — TRIM with '$', with '$,', and LTRIM with '$'
SELECT ROW_ID, AMOUNT_TXT,
       TRIM(AMOUNT_TXT, '$')   AS DOLLAR_ONLY,
       TRIM(AMOUNT_TXT, '$,')  AS DOLLAR_OR_COMMA,
       LTRIM(AMOUNT_TXT, '$')  AS LEFT_DOLLAR
FROM SALES_RAW
ORDER BY ROW_ID;
Result8 rows · 5 columns
ROW_IDAMOUNT_TXTDOLLAR_ONLYDOLLAR_OR_COMMALEFT_DOLLAR
1$4,200.004,200.004,200.004,200.00
2890.50890.50890.50890.50
3$6,1506,1506,1506,150
42,310.752,310.752,310.752,310.75
5N/AN/AN/AN/A
6$640.00640.00640.00640.00
79,250.009,250.009,250.009,250.00
8$1,0801,0801,0801,080

Look carefully. DOLLAR_ONLY, DOLLAR_OR_COMMA and LEFT_DOLLAR are identical on every row.

Adding the comma to the set changed nothing, and switching to LTRIM changed nothing either. No amount in this column begins or ends with a comma, and none of them ends with a $. Characters we name are only removed if they are actually at an end.

It really is a set, and order is meaningless

Let's pass the same two characters in both orders, and then pass some that are not there at all.

Query — the same characters in both orders
SELECT TRIM('$4,200.00', '$,')  AS DOLLAR_THEN_COMMA,
       TRIM('$4,200.00', ',$')  AS COMMA_THEN_DOLLAR,
       TRIM('$4,200.00', '$,.') AS ALSO_STRIP_DOTS,
       TRIM('$4,200.00', 'xyz') AS NONE_OF_THESE_PRESENT;
Result1 row · 4 columns
DOLLAR_THEN_COMMACOMMA_THEN_DOLLARALSO_STRIP_DOTSNONE_OF_THESE_PRESENT
4,200.004,200.004,200.00$4,200.00

'$,' and ',$' give the same answer, which is what “set” means.

NONE_OF_THESE_PRESENT is the control. A set of characters that does not appear at either end leaves the value exactly as it was.

What this grid taught its author

ALSO_STRIP_DOTS is a correction, and it is on the page on purpose. A full stop was added to the set expecting the decimal point to be removed with it. It was not — and the reason is the whole lesson here. That full stop is not at an end; it is in the middle of the number. TRIM works from the outside in and stops at the first character that is not in the set, so it never reaches it.

Which means there is a hard limit

If TRIM stops at the first character not in the set, then anything surrounded by ordinary digits is out of reach. Let's count what is left.

Query — commas still inside after trimming
SELECT ROW_ID, AMOUNT_TXT,
       TRIM(AMOUNT_TXT, '$,') AS AFTER_TRIM,
       REGEXP_COUNT(TRIM(AMOUNT_TXT, '$,'), ',') AS COMMAS_STILL_INSIDE
FROM SALES_RAW
ORDER BY ROW_ID;
Result8 rows · 4 columns
ROW_IDAMOUNT_TXTAFTER_TRIMCOMMAS_STILL_INSIDE
1$4,200.004,200.001
2890.50890.500
3$6,1506,1501
42,310.752,310.751
5N/AN/A0
6$640.00640.000
79,250.009,250.001
8$1,0801,0801

Five of the eight amounts still contain a comma, and TRIM cannot do anything about any of them.

If we are trying to turn this column into a number, TRIM has taken us as far as it goes. The function that removes a character wherever it appears is REPLACE, and that is Episode 67.

What does this fix in a GROUP BY?

Everything so far is a function reference until we put it next to a GROUP BY. This is the part worth remembering.

Let's group the region column exactly as it is stored.

Query — GROUP BY the region text as stored
SELECT REGION_TXT, COUNT(*) AS N
FROM SALES_RAW GROUP BY REGION_TXT ORDER BY REGION_TXT;
Result7 rows · 2 columns
REGION_TXTN
CA-West1
US-EAST1
US-East1
ca-west1
eu-central1
uk-north1
us-east2

Seven groups out of eight rows. And the same region appears three times under three different spellings: us-east, US-EAST and US-East.

That is a report that is wrong, and nothing in it looks broken. Every row has a plausible region name and a plausible count.

Now let's clean the grouping key with the UPPER(TRIM(...)) pair from Episode 64 and group again.

Query — GROUP BY UPPER(TRIM(...))
SELECT UPPER(TRIM(REGION_TXT)) AS REGION, COUNT(*) AS N
FROM SALES_RAW GROUP BY UPPER(TRIM(REGION_TXT)) ORDER BY REGION;
Result4 rows · 2 columns
REGIONN
CA-WEST2
EU-CENTRAL1
UK-NORTH1
US-EAST4

Four groups, and the east-coast rows are together where they belong. Add up the three us-east spellings in the uncleaned grid and you get the count in the US-EAST row here.

Seven down to four is the clean-up doing real work. It is the difference between a regional breakdown we can act on and one that splits a region across three lines.

Why nobody catches this

The reason this matters more than it looks is that the broken version never errors. Both grids are valid SQL returning valid results, and only one of them answers the question that was asked. Normalise the key before you group on human-typed text — every time, not when you suspect a problem.

Now let's pad a value to a fixed width

Padding is the opposite job. We add characters until the value reaches a target length. LPAD adds them on the left, RPAD on the right, and both take the target length and the character to pad with.

The use everybody actually has for LPAD is a zero-padded reference built from an integer id.

Query — LPAD for a fixed-width reference
SELECT CUSTOMER_ID,
       LPAD(CUSTOMER_ID, 8, '0')  AS PADDED_8,
       LPAD(CUSTOMER_ID, 6, '0')  AS PADDED_6,
       'CUST-' || LPAD(CUSTOMER_ID, 6, '0') AS REFERENCE
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;
Result10 rows · 4 columns
CUSTOMER_IDPADDED_8PADDED_6REFERENCE
10100000101000101CUST-000101
10200000102000102CUST-000102
10300000103000103CUST-000103
10400000104000104CUST-000104
10500000105000105CUST-000105
10600000106000106CUST-000106
10700000107000107CUST-000107
10800000108000108CUST-000108
10900000109000109CUST-000109
11000000110000110CUST-000110

Two widths side by side, and then the real thing: a prefix concatenated onto the padded id, which gives us a reference of a constant shape.

Fixed-width references sort correctly as text, which is the whole reason to build them. Without the zeros, CUST-9 sorts after CUST-110.

RPAD lines a column up instead, which is how we get readable output in a tool that will not format for us.

Query — RPAD to 22, and LPAD with dots to 12
SELECT '[' || RPAD(CUSTOMER_NAME, 22, ' ') || ']' AS NAME_COLUMN,
       '[' || LPAD(SEGMENT, 12, '.') || ']'       AS SEGMENT_COLUMN
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;
Result10 rows · 2 columns
NAME_COLUMNSEGMENT_COLUMN
[Northwind Traders ][..Enterprise]
[Blue Ridge Supply ][.........SMB]
[Kestrel Analytics ][..Enterprise]
[Harbour Foods ][.........SMB]
[Alpine Logistics ][..Enterprise]
[Delta Print Co ][.........SMB]
[Meridian Health ][..Enterprise]
[Copper Kettle Ltd ][.........SMB]
[Vantage Media ][..Enterprise]
[Sable Interiors ][.........SMB]

Names padded to 22 with spaces and segments right-aligned to 12 with dots, with brackets so we can see the alignment.

Leader dots are an old typesetting trick and they still work. The eye follows them across the gap.

What happens when the width is too small?

So what happens when the target length is shorter than the value? Nothing good, and nothing loud.

Let's pad the customer names to 8 in both directions.

Query — LPAD and RPAD to a target that is too small
SELECT CUSTOMER_ID, CUSTOMER_NAME,
       LENGTH(CUSTOMER_NAME) AS REAL_LEN,
       LPAD(CUSTOMER_NAME, 8, '.') AS LPAD_TO_8,
       RPAD(CUSTOMER_NAME, 8, '.') AS RPAD_TO_8
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;
Result10 rows · 5 columns
CUSTOMER_IDCUSTOMER_NAMEREAL_LENLPAD_TO_8RPAD_TO_8
101Northwind Traders17NorthwinNorthwin
102Blue Ridge Supply17Blue RidBlue Rid
103Kestrel Analytics17Kestrel Kestrel
104Harbour Foods13Harbour Harbour
105Alpine Logistics16Alpine LAlpine L
106Delta Print Co14Delta PrDelta Pr
107Meridian Health15MeridianMeridian
108Copper Kettle Ltd17Copper KCopper K
109Vantage Media13Vantage Vantage
110Sable Interiors15Sable InSable In

It truncates. Northwind Traders comes back as Northwin, and the REAL_LEN column is there so we can see how much went missing.

Now look harder at LPAD_TO_8 and RPAD_TO_8. They returned exactly the same value on every row.

LPAD did not keep the right-hand end of the string, which is what we would reasonably guess from a function whose job is to add characters on the left. Both of them keep the front and discard the tail.

Let's count the damage.

Query — how much damage, counted
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(LENGTH(CUSTOMER_NAME) > 8) AS LONGER_THAN_8,
       COUNT_IF(RPAD(CUSTOMER_NAME, 8, '.') <> CUSTOMER_NAME) AS CHANGED_BY_RPAD_8
FROM CUSTOMERS;
Result1 row · 3 columns
ROWS_TOTALLONGER_THAN_8CHANGED_BY_RPAD_8
101010
This is the one to remember

Ten rows in the table, all ten longer than the target, and all ten changed. Every single row lost data, with no error and no warning. A padding call is not a safe way to fit a value into a column — it is a silent truncation with a reassuring name. If a value being too long matters, test for it with LENGTH first and decide what to do about it yourself.

Two smaller edges

What if the pad argument is more than one character?

Query — a multi-character pad
SELECT LPAD('7', 10, 'ab')   AS PAD_WITH_AB,
       RPAD('7', 10, '-=')   AS PAD_WITH_DASH_EQUALS,
       LPAD('7', 10, ' ')    AS PAD_WITH_SPACE,
       LENGTH(LPAD('7', 10, 'ab')) AS RESULT_LENGTH;
Result1 row · 4 columns
PAD_WITH_ABPAD_WITH_DASH_EQUALSPAD_WITH_SPACERESULT_LENGTH
ababababa77-=-=-=-=- 710

It repeats until the target length is reached and then gets cut mid-pattern. The result is the requested 10 characters long, not a whole number of repetitions. Useful, as long as we are not expecting the pattern to come out whole.

And what about a NULL, or an empty string?

Query — padding NULL, and padding an empty string
SELECT LPAD(NULL, 6, '0') AS LPAD_NULL,
       RPAD(NULL, 6, '0') AS RPAD_NULL,
       LPAD('', 6, '0')   AS LPAD_EMPTY,
       TRIM(NULL)         AS TRIM_NULL;
Result1 row · 4 columns
LPAD_NULLRPAD_NULLLPAD_EMPTYTRIM_NULL
NULLNULL000000NULL

LPAD(NULL, ...) is NULL and RPAD(NULL, ...) is NULL. That is the same rule as the concatenation in Episode 64, and TRIM(NULL) behaves the same way.

But LPAD('', 6, '0') gives us six zeros, because an empty string is a real string of length zero and padding it is a perfectly sensible request.

That pair is worth internalising. A NULL id pads to nothing, and an empty-string id pads to a reference made entirely of zeros. Both are wrong in a report, and they are wrong in two different ways that need two different fixes.

Let's put all of it in one query

One query with everything from this post on the real messy table: the stored value in brackets, the trimmed value in brackets, the raw amount, the amount with its end characters stripped, and a padded reference built from the row id.

Query — raw, trimmed, stripped, padded
SELECT ROW_ID,
       '[' || CUSTOMER_TXT || ']' AS RAW,
       '[' || TRIM(CUSTOMER_TXT) || ']' AS TRIMMED,
       AMOUNT_TXT AS RAW_AMOUNT,
       TRIM(AMOUNT_TXT, '$,') AS AMOUNT_ENDS_STRIPPED,
       'CUST-' || LPAD(ROW_ID, 6, '0') AS REFERENCE
FROM SALES_RAW
ORDER BY ROW_ID;
Result8 rows · 6 columns · scroll →
ROW_IDRAWTRIMMEDRAW_AMOUNTAMOUNT_ENDS_STRIPPEDREFERENCE
1[ Northwind Traders ][Northwind Traders]$4,200.004,200.00CUST-000001
2[blue ridge supply][blue ridge supply]890.50890.50CUST-000002
3[KESTREL ANALYTICS][KESTREL ANALYTICS]$6,1506,150CUST-000003
4[Harbour Foods ][Harbour Foods]2,310.752,310.75CUST-000004
5[Alpine Logistics][Alpine Logistics]N/AN/ACUST-000005
6[ Delta Print Co][Delta Print Co]$640.00640.00CUST-000006
7[Meridian Health][Meridian Health]9,250.009,250.00CUST-000007
8[copper kettle ltd][copper kettle ltd]$1,0801,080CUST-000008

TRIMMED is a column we could hand to someone. AMOUNT_ENDS_STRIPPED is not, because of the commas TRIM could not reach.

Finishing that job needs a function that works on the middle of a value, and that is where this level goes next.

Key points

  • TRIM removes from both ends, LTRIM from the left and RTRIM from the right. Plain TRIM is the one you want most of the time.
  • Trimming never touches the middle of a value, and that is correct — every name on this page still has its internal space after trimming, because the space between two words is part of the name.
  • COUNT_IF(x <> TRIM(x)) tells you how many rows a cleaning step would change before you run it. Here it was 3 rows out of 8, with one row counted in both the leading and the trailing figure.
  • The second argument is a set of characters, not a substring. TRIM(x, '$,') removes any $ or , found at an end, and '$,' and ',$' behave identically.
  • TRIM stops at the first character that is not in the set, so it cannot reach a comma or a decimal point in the middle of a number. That is REPLACE's job, in Episode 67.
  • Cleaning a grouping key with UPPER(TRIM(...)) took the region column from 7 groups to 4. The uncleaned version returned no error and a wrong answer.
  • LPAD builds fixed-width references that sort correctly as text; RPAD lines columns up for reading.
  • When the target length is shorter than the value, both pad functions truncate, and both keep the front. LPAD does not keep the right-hand end. All ten rows here lost data with no error.
  • A multi-character pad repeats and is cut mid-pattern to hit the exact target length.
  • Padding a NULL gives NULL; padding an empty string gives you a value made entirely of padding. Two different bugs needing two different fixes.
Next in this series

Episode 66 — SUBSTR and POSITION. Pulling pieces out of a string by offset and by search, rather than removing things from its edges.

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 trim-with-a-full-stop result that contradicts what the episode expected. The SALES_RAW and CUSTOMERS 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.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.