Snowflake SQL Tutorial 66 - SUBSTR, LEFT, RIGHT, POSITION, CHARINDEX - Strings 3

Snowflake SQL · Episode 66

In this post we are going to cover SUBSTR, LEFT, RIGHT, POSITION and CHARINDEX in Snowflake.

These are the functions that cut a piece out of a string. SUBSTR takes a piece from wherever you tell it to start. LEFT and RIGHT take a piece off one end. POSITION and CHARINDEX do the other half of the job: they tell you where something is in the string, so you know where to cut.

We will start with the simplest SUBSTR and build it up one step at a time. By the end we will take an email address apart into four pieces using nothing but these functions, and then write a query that checks the pieces are right.

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

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

On this page
  1. Let's look at the two tables we will use
  2. Let's start with SUBSTR
  3. Now let's find out where character one is
  4. Now let's take a piece off one end with LEFT and RIGHT
  5. Now let's find where something is with POSITION and CHARINDEX
  6. What happens when POSITION finds nothing?
  7. Now let's find the second occurrence
  8. Let's take an email apart with nothing else

Let's look at the two tables we will use

Before we cut anything up we need something to cut. Everything below runs against two tables from this course's setup script, so run that script once and you are ready.

Setup — run once
-- EMPLOYEES and CUSTOMERS come from the setup script this course uses
-- throughout. Run it once; every episode reuses the same tables.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

-- Nothing on this page modifies either table. Every statement below is a
-- SELECT, so you can run them in any order and run them twice.

Let's look at the first one.

Query — EMPLOYEES
SELECT EMP_ID, FIRST_NAME, LAST_NAME, EMAIL
FROM EMPLOYEES
ORDER BY EMP_ID;
Result8 rows · 4 columns
EMP_IDFIRST_NAMELAST_NAMEEMAIL
1AishaKhanaisha.khan@example.com
2MarcusReidmarcus.reid@example.com
3LenaFischerlena.fischer@example.com
4TomasNovaktomas.novak@example.com
5PriyaRamanpriya.raman@example.com
6DanielO'Connordaniel.oconnor@example.com
7SofiaMarinosofia.marino@example.com
8GraceLingrace.lin@example.com

Eight rows. The column we care about here is EMAIL, and every address follows the same first.last@example.com shape. That matters later. Because FIRST_NAME and LAST_NAME are sitting right there in their own columns, we will be able to check our work against them.

Now the second one.

Query — CUSTOMERS
SELECT CUSTOMER_ID, CUSTOMER_NAME, CITY, COUNTRY
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;
Result10 rows · 4 columns
CUSTOMER_IDCUSTOMER_NAMECITYCOUNTRY
101Northwind TradersCharlotteUSA
102Blue Ridge SupplyAshevilleUSA
103Kestrel AnalyticsTorontoCanada
104Harbour FoodsVancouverCanada
105Alpine LogisticsZurichNULL
106Delta Print CoRaleighUSA
107Meridian HealthBostonUSA
108Copper Kettle LtdManchesterUK
109Vantage MediaLondonUK
110Sable InteriorsAustinUSA

Ten rows, and the names are deliberately different lengths. Harbour Foods is one of the shortest and Copper Kettle Ltd one of the longest. That is what we want for testing. When we cut at a fixed position, the cut lands somewhere different in every row.

Let's start with SUBSTR

SUBSTR takes three things: the value, the position to start at, and how many characters to take. The third one is optional. Leave it off and you get everything from the start position to the end of the string.

Let's cut the customer name three different ways in one query.

Query
SELECT CUSTOMER_ID, CUSTOMER_NAME,
       SUBSTR(CUSTOMER_NAME, 1, 5)  AS FIRST_5,
       SUBSTR(CUSTOMER_NAME, 3, 4)  AS FROM_3_TAKE_4,
       SUBSTR(CUSTOMER_NAME, 6)     AS FROM_6_TO_END
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;
Result10 rows · 5 columns
CUSTOMER_IDCUSTOMER_NAMEFIRST_5FROM_3_TAKE_4FROM_6_TO_END
101Northwind TradersNorthrthwwind Traders
102Blue Ridge SupplyBlue ue RRidge Supply
103Kestrel AnalyticsKestrstreel Analytics
104Harbour FoodsHarborbouur Foods
105Alpine LogisticsAlpinpinee Logistics
106Delta Print CoDeltalta Print Co
107Meridian HealthMeridridiian Health
108Copper Kettle LtdCoppepperr Kettle Ltd
109Vantage MediaVantantagge Media
110Sable InteriorsSableble Interiors

FIRST_5 takes five characters from the beginning. Look at Blue Ridge Supply. It comes back as Blue and a space, because the space is a character like any other and nothing trims it for us.

FROM_3_TAKE_4 starts in the middle instead. FROM_6_TO_END has no length argument at all, so it runs to the end of each name.

Two names, one function

SUBSTR and SUBSTRING are two names for one function in Snowflake. We will prove that with a query rather than take it on trust, in the SUBSTRING is the same function query further down. This page writes SUBSTR throughout because it is shorter to type.

Now let's find out where character one is

This is the part worth running rather than remembering. Does counting start at 0 or at 1? And what does a start position of 0 do?

Let's ask Snowflake all three questions at once.

Query
SELECT SUBSTR('Snowflake', 0, 4) AS START_AT_0,
       SUBSTR('Snowflake', 1, 4) AS START_AT_1,
       SUBSTR('Snowflake', 2, 4) AS START_AT_2,
       LENGTH(SUBSTR('Snowflake', 0, 4)) AS LEN_FROM_0,
       LENGTH(SUBSTR('Snowflake', 1, 4)) AS LEN_FROM_1;
Result1 row · 5 columns
START_AT_0START_AT_1START_AT_2LEN_FROM_0LEN_FROM_1
SnowSnownowf44

START_AT_0 and START_AT_1 came back as the same string, and LEN_FROM_0 and LEN_FROM_1 are both 4. So they are the same length too, not just similar looking. Snowflake treats a start position of 0 as a synonym for 1. It is not an off-by-one, and it is not rejected.

That makes START_AT_2 the column to look at. Position 2 gave us a result beginning with n, so position 2 is the second character and not the third. SUBSTR is one-based, and 0 is just folded into 1.

Now let's try a negative start

A negative start counts from the right-hand end instead. Let's see what that gives us.

Query
SELECT SUBSTR('Snowflake', -4)    AS LAST_4,
       SUBSTR('Snowflake', -4, 2) AS LAST_4_TAKE_2,
       SUBSTR('Snowflake', -1)    AS LAST_1,
       SUBSTR('Snowflake', -20)   AS MORE_THAN_THE_LENGTH;
Result1 row · 4 columns
LAST_4LAST_4_TAKE_2LAST_1MORE_THAN_THE_LENGTH
lakelae

-4 starts four characters from the right, so LAST_4 is the last four characters and we never had to know how long the string was. Add a length and you get part of the tail instead: LAST_4_TAKE_2 takes two characters starting four from the end.

MORE_THAN_THE_LENGTH is the one to notice. -20 points further left than the string is long, and what came back is an empty string, not an error.

Now let's ask for more than is there

Four different ways of going off the end, in one query.

Query
SELECT SUBSTR('Snowflake', 5, 100) AS PLENTY_ASKED,
       SUBSTR('Snowflake', 20, 5)  AS START_PAST_THE_END,
       SUBSTR('Snowflake', 5, 0)   AS TAKE_ZERO,
       SUBSTR(NULL, 1, 3)          AS SUBSTR_OF_NULL;
Result1 row · 4 columns
PLENTY_ASKEDSTART_PAST_THE_ENDTAKE_ZEROSUBSTR_OF_NULL
flakeNULL

Four quiet answers. Asking for 100 characters gives us what is there. Starting past the end gives an empty string. A length of 0 gives an empty string. And SUBSTR of NULL is NULL.

The theme of this episode

Nothing in that grid raised an error. An out-of-range start, an over-long length and a NULL input all gave us a value the query will happily carry on with. An empty string is not a NULL, so a WHERE ... IS NOT NULL will not catch it either. If a column that should hold a code suddenly holds blanks, a SUBSTR whose arithmetic went off the end of the string is the first place to look.

And is SUBSTRING really the same function?

Rather than assert it, let's write the two forms side by side and ask Snowflake to compare them for us.

Query
SELECT SUBSTR('Snowflake', 2, 4)    AS SUBSTR_FORM,
       SUBSTRING('Snowflake', 2, 4) AS SUBSTRING_FORM,
       SUBSTR('Snowflake', 2, 4) = SUBSTRING('Snowflake', 2, 4) AS SAME;
Result1 row · 3 columns
SUBSTR_FORMSUBSTRING_FORMSAME
nowfnowfTRUE

SUBSTR_FORM and SUBSTRING_FORM hold the same value, and SAME is TRUE. One function, two names.

Now let's take a piece off one end with LEFT and RIGHT

When the piece we want is at one end of the string, LEFT and RIGHT say so in fewer characters than SUBSTR does.

Let's put the SUBSTR equivalent next to LEFT so we can read them against each other.

Query
SELECT CUSTOMER_ID, CUSTOMER_NAME,
       LEFT(CUSTOMER_NAME, 4)  AS LEFT_4,
       RIGHT(CUSTOMER_NAME, 4) AS RIGHT_4,
       SUBSTR(CUSTOMER_NAME, 1, 4) AS SAME_AS_LEFT_4
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;
Result10 rows · 5 columns
CUSTOMER_IDCUSTOMER_NAMELEFT_4RIGHT_4SAME_AS_LEFT_4
101Northwind TradersNortdersNort
102Blue Ridge SupplyBluepplyBlue
103Kestrel AnalyticsKestticsKest
104Harbour FoodsHarboodsHarb
105Alpine LogisticsAlpiticsAlpi
106Delta Print CoDeltt CoDelt
107Meridian HealthMerialthMeri
108Copper Kettle LtdCopp LtdCopp
109Vantage MediaVantediaVant
110Sable InteriorsSabliorsSabl

LEFT_4 and SAME_AS_LEFT_4 hold the same value in every row. That is the claim, so let's not leave it to the eye. Let's count it.

Query
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(LEFT(CUSTOMER_NAME, 4) = SUBSTR(CUSTOMER_NAME, 1, 4)) AS LEFT_MATCHES,
       COUNT_IF(RIGHT(CUSTOMER_NAME, 4) = SUBSTR(CUSTOMER_NAME, -4))  AS RIGHT_MATCHES
FROM CUSTOMERS;
Result1 row · 3 columns
ROWS_TOTALLEFT_MATCHESRIGHT_MATCHES
101010

Ten rows in the table. Ten rows where LEFT(..., 4) equals SUBSTR(..., 1, 4), and ten rows where RIGHT(..., 4) equals SUBSTR(..., -4). So both shorthands hold on every row, not just on the ones we happened to read.

ShorthandThe SUBSTR that does the same job
LEFT(S, n)SUBSTR(S, 1, n)
RIGHT(S, n)SUBSTR(S, -n)

Now let's ask them for more characters than exist

Query
SELECT LEFT('abc', 10) AS LEFT_10, RIGHT('abc', 10) AS RIGHT_10,
       LENGTH(LEFT('abc', 10)) AS LEFT_LEN,
       LEFT('abc', 0) AS LEFT_0;
Result1 row · 4 columns
LEFT_10RIGHT_10LEFT_LENLEFT_0
abcabc3

We asked LEFT for ten characters of a three-character string and got the three that exist. LEFT_LEN is 3, which proves the result really is that short and was not padded out with spaces. LEFT_0 shows that a length of 0 gives an empty string.

If you wanted padding

Padding a value out to a fixed width is a different job, and a different pair of functions: LPAD and RPAD, which were Episode 65. LEFT and RIGHT only ever take characters away.

Now let's find where something is with POSITION and CHARINDEX

Cutting at a fixed position only helps when we know the position. Most of the time we have to find it first.

Snowflake gives us the same search under two names, and the argument order is the thing people get wrong. So let's not guess. Let's run both functions with the same two arguments and put the answers next to each other.

Query
SELECT EMAIL,
       POSITION('@', EMAIL)  AS POSITION_NEEDLE_FIRST,
       CHARINDEX('@', EMAIL) AS CHARINDEX_NEEDLE_FIRST
FROM EMPLOYEES
ORDER BY EMP_ID;
Result8 rows · 3 columns
EMAILPOSITION_NEEDLE_FIRSTCHARINDEX_NEEDLE_FIRST
aisha.khan@example.com1111
marcus.reid@example.com1212
lena.fischer@example.com1313
tomas.novak@example.com1212
priya.raman@example.com1212
daniel.oconnor@example.com1515
sofia.marino@example.com1313
grace.lin@example.com1010

POSITION_NEEDLE_FIRST and CHARINDEX_NEEDLE_FIRST are identical on all eight rows. Both functions take what you are looking for first and the string to search second. Both columns came from the same pair of arguments, so that is settled now rather than remembered.

The numbers are one-based, the same as SUBSTR. 11 for aisha.khan@example.com means the @ is its eleventh character.

And there is a third way to write it

Query
SELECT EMAIL,
       POSITION('@' IN EMAIL) AS WITH_IN_KEYWORD,
       POSITION('@', EMAIL)   AS WITH_COMMA
FROM EMPLOYEES
ORDER BY EMP_ID;
Result8 rows · 3 columns
EMAILWITH_IN_KEYWORDWITH_COMMA
aisha.khan@example.com1111
marcus.reid@example.com1212
lena.fischer@example.com1313
tomas.novak@example.com1212
priya.raman@example.com1212
daniel.oconnor@example.com1515
sofia.marino@example.com1313
grace.lin@example.com1010

WITH_IN_KEYWORD uses POSITION(needle IN haystack), which reads like standard SQL, and it produced the same numbers as WITH_COMMA. Three spellings, one behaviour. Use whichever your team reads most easily.

SpellingArgument order
POSITION(needle, haystack)What you are looking for, then where to look
POSITION(needle IN haystack)Same, written as standard SQL
CHARINDEX(needle, haystack)Same again, under the name other databases use

What happens when POSITION finds nothing?

Every search function has to answer that question, and the answer Snowflake gives is the one most likely to hurt you quietly. Let's run it.

Query
SELECT POSITION('z', 'Snowflake')  AS PRESENT,
       POSITION('Q', 'Snowflake')  AS ABSENT,
       POSITION('', 'Snowflake')   AS EMPTY_NEEDLE,
       POSITION('a', NULL)         AS HAYSTACK_IS_NULL;
Result1 row · 4 columns
PRESENTABSENTEMPTY_NEEDLEHAYSTACK_IS_NULL
001NULL
About that first column

The alias on PRESENT is wrong, and the value is right. That query asks for the position of z in Snowflake and calls the answer PRESENT. There is no z in Snowflake, so the honest answer is 0, and 0 is what came back. The episode leaves the mistake on screen rather than re-recording it, because it makes the better point: read the data, not the column name. An alias is a label a human typed. It is not evidence of anything.

Past the alias, the grid carries the rule. Not found is 0. Not NULL, and not -1 as several other languages return. 0 is an ordinary number, so arithmetic built on it succeeds and gives us nonsense.

That is exactly how the email surgery we are about to write could fail on data it was not written for. LEFT(EMAIL, POSITION('@', EMAIL) - 1) is correct while the @ is there. On a row without one, POSITION returns 0, the subtraction quietly gives -1, and LEFT is handed a length nobody intended. Nothing in the arithmetic objected on the way there.

EMPTY_NEEDLE and HAYSTACK_IS_NULL are the other two edges. Searching for an empty string returns 1, and searching inside a NULL returns NULL. So a missing needle and a missing haystack give us two different answers, and only one of them is a number.

Now let's find the second occurrence

POSITION takes an optional third argument: the position to start searching from. That is what turns a single search into a way of walking along a string. Find the first occurrence, then search again from just past it.

Let's find both dots in each email address.

Query
SELECT EMAIL,
       POSITION('.', EMAIL)                        AS FIRST_DOT,
       POSITION('.', EMAIL, POSITION('.', EMAIL) + 1) AS SECOND_DOT
FROM EMPLOYEES
ORDER BY EMP_ID;
Result8 rows · 3 columns
EMAILFIRST_DOTSECOND_DOT
aisha.khan@example.com619
marcus.reid@example.com720
lena.fischer@example.com521
tomas.novak@example.com620
priya.raman@example.com620
daniel.oconnor@example.com723
sofia.marino@example.com621
grace.lin@example.com618

For aisha.khan@example.com the dots are at 6 and 19. The first one separates the names and the second one sits inside the domain.

The inner POSITION finds the first dot, and the outer call starts one character later. That is why SECOND_DOT cannot be the first dot found all over again.

There is a shorter way coming

Writing the same POSITION call twice inside one expression is correct but hard to read, and it gets worse for a third occurrence. Episode 67 replaces the whole pattern with SPLIT_PART, which asks for the piece you want by number instead.

Let's take an email apart with nothing else

Those two functions are enough to pull an email address into its parts. POSITION finds each separator, and SUBSTR or LEFT takes the piece between them.

The surname is the only hard one. It sits between two separators, so its length has to be worked out by subtraction. Let's write all four pieces in one query.

Query
SELECT EMAIL,
       LEFT(EMAIL, POSITION('@', EMAIL) - 1)                      AS LOCAL_PART,
       SUBSTR(EMAIL, POSITION('@', EMAIL) + 1)                    AS DOMAIN_PART,
       LEFT(EMAIL, POSITION('.', EMAIL) - 1)                      AS FIRST_NAME_FROM_EMAIL,
       SUBSTR(EMAIL, POSITION('.', EMAIL) + 1,
              POSITION('@', EMAIL) - POSITION('.', EMAIL) - 1)    AS LAST_NAME_FROM_EMAIL
FROM EMPLOYEES
ORDER BY EMP_ID;
Result8 rows · 5 columns
EMAILLOCAL_PARTDOMAIN_PARTFIRST_NAME_FROM_EMAILLAST_NAME_FROM_EMAIL
aisha.khan@example.comaisha.khanexample.comaishakhan
marcus.reid@example.commarcus.reidexample.commarcusreid
lena.fischer@example.comlena.fischerexample.comlenafischer
tomas.novak@example.comtomas.novakexample.comtomasnovak
priya.raman@example.compriya.ramanexample.compriyaraman
daniel.oconnor@example.comdaniel.oconnorexample.comdanieloconnor
sofia.marino@example.comsofia.marinoexample.comsofiamarino
grace.lin@example.comgrace.linexample.comgracelin

Local part, domain, first name and surname, all out of one column of text. Read the LAST_NAME_FROM_EMAIL expression twice. It starts one character after the dot and takes POSITION('@', EMAIL) - POSITION('.', EMAIL) - 1 characters, which is the distance between the two separators.

It looks right, so now let's check it

That grid is convincing, and that is the problem with it. Luckily EMPLOYEES holds the real first and last names in their own columns, so we can check the extraction against them instead of eyeballing it.

Query
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(UPPER(LEFT(EMAIL, POSITION('.', EMAIL) - 1)) = UPPER(FIRST_NAME))
         AS FIRST_NAME_MATCHES,
       COUNT_IF(UPPER(SUBSTR(EMAIL, POSITION('.', EMAIL) + 1,
                POSITION('@', EMAIL) - POSITION('.', EMAIL) - 1))
                = UPPER(REPLACE(LAST_NAME, '''', ''))) AS LAST_NAME_MATCHES
FROM EMPLOYEES;
Result1 row · 3 columns
ROWS_TOTALFIRST_NAME_MATCHESLAST_NAME_MATCHES
888

Eight rows, eight first names matching, eight surnames matching.

One caveat belongs with that result. The comparison strips apostrophes with REPLACE(LAST_NAME, '''', ''), and that is what lets O'Connor match the oconnor in the address. Without it the check would report a disagreement, and it would be right to, because the email really does not contain the apostrophe.

A count of matches is only half a check, though. It tells us how many rows agreed. It does not show us a row that did not.

So let's run the same comparison the other way round, as a WHERE clause that can only return rows where the extraction is wrong.

Query
SELECT EMP_ID, FIRST_NAME, LAST_NAME, EMAIL,
       SUBSTR(EMAIL, POSITION('.', EMAIL) + 1,
              POSITION('@', EMAIL) - POSITION('.', EMAIL) - 1) AS FROM_EMAIL
FROM EMPLOYEES
WHERE UPPER(SUBSTR(EMAIL, POSITION('.', EMAIL) + 1,
            POSITION('@', EMAIL) - POSITION('.', EMAIL) - 1))
      <> UPPER(REPLACE(LAST_NAME, '''', ''))
ORDER BY EMP_ID;
Result0 rows · 5 columns
EMP_IDFIRST_NAMELAST_NAMEEMAILFROM_EMAIL

Nothing came back. That is a query written so that it could fail, shown returning an empty grid. It is worth more than the count of matches, because a disagreement would have arrived here as a visible row with the two values next to each other, rather than as a number that was one lower than we expected.

And the whole thing on one row

Query
SELECT EMAIL,
       POSITION('@', EMAIL) AS AT_SIGN_AT,
       LEFT(EMAIL, POSITION('@', EMAIL) - 1) AS LOCAL_PART,
       RIGHT(EMAIL, LENGTH(EMAIL) - POSITION('@', EMAIL)) AS DOMAIN_THE_OTHER_WAY,
       SUBSTR(EMAIL, POSITION('@', EMAIL) + 1) AS DOMAIN_WITH_SUBSTR
FROM EMPLOYEES
WHERE EMP_ID = 1;
Result1 row · 5 columns
EMAILAT_SIGN_ATLOCAL_PARTDOMAIN_THE_OTHER_WAYDOMAIN_WITH_SUBSTR
aisha.khan@example.com11aisha.khanexample.comexample.com

One address, taken apart, with the domain pulled out two different ways. DOMAIN_THE_OTHER_WAY uses RIGHT with a length worked out from LENGTH and POSITION. DOMAIN_WITH_SUBSTR uses SUBSTR with no length at all. They agree, and the second one is obviously easier to get right.

This is also a fair picture of what hand-rolled string surgery costs. Four nested calls and two subtractions, to answer a question that Episode 67 answers with one function and a number.

Key points

  • SUBSTR is one-based, and a start position of 0 behaves exactly like 1 in Snowflake — it is not an off-by-one and it is not an error. Position 2 is the second character.
  • A negative start counts from the right-hand end, so SUBSTR(S, -4) is the last four characters without needing LENGTH.
  • Going past either end gives an empty string, not an error: a start beyond the string, a start further left than the string is long, and a length of 0 all return ''. SUBSTR of NULL is NULL.
  • SUBSTRING is the same function as SUBSTR, settled on the page by comparing the two forms in SQL.
  • LEFT(S, n) is SUBSTR(S, 1, n) and RIGHT(S, n) is SUBSTR(S, -n) — counted as equal on all ten rows rather than asserted. Neither one pads.
  • POSITION and CHARINDEX are the same search, and both take what you are looking for first. POSITION(needle IN haystack) is a third spelling of it.
  • Not found is 0, not NULL and not -1. Arithmetic on that 0 succeeds, which is how a missing separator becomes a wrong answer instead of a failure.
  • POSITION's optional third argument is where to start searching, which is how you reach the second and later occurrences.
  • When you build a value out of string functions, write the check that could fail — a count of matches plus a query that returns the rows where it disagreed.
Next in this series

Episode 67 — REPLACE, TRANSLATE, SPLIT, SPLIT_PART and STRTOK. The last of the string functions, including the one that does this episode's email surgery in a single call — and the two splitters that disagree about how many fields a string has.

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 empty grid. The EMPLOYEES and CUSTOMERS data is invented demo data created by that 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.

Subscribe to: Post Comments (Atom)