SELECT Statement in Snowflake and Databricks

The SELECT statement in Snowflake continues to amazed me. I've mentioned about the EXCLUDE clause before (link) which enables us to select all columns except one or two. And I've mentioned about the time travel, which is very cool. Today I'm going to write about a SELECT statement feature in Snowflake which is not available in SQL Server or Synapse. This wonderful feature is available in Databricks too.

Say you need to process column 1 and column 2 from table 1, and then combine them.

So need to write a CTE, like this:

WITH CTE1 AS
( SELECT 
	SomeFunction(Column1) AS X,
	SomeFunction(Column2) as Y
  FROM Table1
  WHERE ...
)
SELECT CONCAT(X,' ',Y) AS Z
FROM CTE1        

Sometimes we end up defining multiple layers of CTEs in order to process the output of the previous process.

Like this:

WITH CTE1 AS
( ... ),
CTE2 AS
( SELECT ...
  FROM CTE1
),
CTE3 AS
( SELECT ...
  FROM CTE2
)
SELECT ...
FROM CTE3         

That era in SQL Server or Oracle is now gone. In Snowflake and Databricks in the same select we can access the previously mentioned column.

Like this:

SELECT 
	SomeFunction(Column1) AS X,
	SomeFunction(X) AS Y1,
	SomeFunction(X) AS Y2,
	SomeFunction(X) AS Y3,
FROM ...        

You see, previously in SQL Server and Oracle we would need to put X in CTE1, so that we can use it multiple times for Y1, Y2, Y3.

But in Snowflake and Databricks we can just put X as the first column, and then all subsequent columns can access X. Very useful, isn't it?

If you haven't tried it before try it as soon as you can. You'll find it a useful feature to have. Whether you use Snowflake or Databricks, you've got to try this feature. It's cool.

The other thing that continue to amaze me in Snow SQL is the regex. Here's an example that I did this week: finding the last space in a string. It's as simple as this:

SELECT Column1, REGEXP_INSTR(Column1, '[ ][^ ]*$') AS LAST_SPACE
FROM Table1        

Quite simple, isn't it?

The [ ] means a space.

The *$ means "search until the end of the string"

So if you do REGEXP_INSTR(Column1, '[ ]*$') you'll find the last space right?

Wrong, it will return you the length of the string plus 1. It is as if it thinks that the end of the string is a space.

To do it we put a caret (^). In regex a caret means "not". So [^ ] means not a space.

So [ ][^ ] means: "a space followed by not space".

This way it will find that last space in the string.

Thanks to JNevill for this technique: link.