Press "Enter" to skip to content

Category: Syntax

Understanding String Concatenation with FOR XML PATH

Published 2023-07-12 by Kevin Feasel

Brent Ozar did some noodling:

The first time I saw FOR XML PATH being used to generate a comma-delimited list, I think I stared at it, shook my head to clear the cobwebs, stared at it some more, and then closed the code editor thinking it was complete witchcraft.

And that same thing probably happened the next several times, too.

But eventually, I took a deep breath and read the code more closely to understand what it was doing.

I do like to joke that this is cryptic code that gets handed down from generation to generation, with each generation saying “Don’t touch the code, for you do not understand it.” But as of SQL Server 2017, you don’t need to do this anymore and can use STRING_AGG().

Comments closed

String Casing in Snowflake

Published 2023-07-06 by Kevin Feasel

Kevin Wilkie is on the case:

When you’re working with a database, it’s very hard to not deal with strings at some point in your journey. There are lots of different functions that you will be working with when you’re working with strings. Today, I want to go over some of the basic ones that you’ll use in Snowflake.

The first two that you’ll deal with make the string either upper or lowercase. Yes, that’s right – you’ve probably figured out the names of the functions already. UPPER() and LOWER() are the 2 functions respectively.

Kevin mentions title capitalization (though not by name) and the quick rule depends on which rulebook you’re using. I grew up with MLA, which I summarize as:

  • Don’t capitalize articles (the, a, an), prepositions, or coordinating conjunctions (for, and, nor, but, or, yet, so)
  • Don’t capitalize “to” when it’s an infinitive (to go, to drive, etc.)
  • Don’t capitalize the second part of a hyphenated phrase if it shows up in the dictionary as one word without a hyphen
  • Capitalize everything else

And a quick bit of advice: understanding title capitalization really does make you look more professional, I promise. Unless we’re using different rulebooks, in which case at least one of us is a heretic.

Comments closed

Generating Random Data in Snowflake

Published 2023-06-28 by Kevin Feasel

Kevin Wilkie generates some random data:

One of the many things that the business team asks me to do is to create random-ish data. Thankfully, in Snowflake, there are many ways to make this happen. Today, I want to go thru just a few of them.

Perhaps the one that most people are familiar with is making Snowflake create a random number.

Click through for initial coverage of the RANDOM() function, as well as how you can generate data across a uniform distribution over a given range.

Comments closed

Indexing Multiple Columns in Oracle with DBMS_SEARCH

Published 2023-06-27 by Kevin Feasel

Brendan Tierney rounds up the usual suspects:

This type of index is a little different to your traditional index. With DBMS_SEARCH we can create an index across multiple schema objects using just a single index. This gives us greater indexing capabilities for scenarios where we need to search data across multiple objects. You can create a ubiquitous search index on multiple columns of a table or multiple columns from different tables in a given schema. All done using one index, rather than having to use multiples. Because of this wider search capability, you will see this (DBMS_SEARCH) being referred to as a Ubiquitous Search Index. A ubiquitous search index is a JSON search index and can be used for full-text and range-based searches.

This is an interesting approach to the problem, though as I think about it, it makes me wonder, if you’re constantly searching in A+B+C+D, is that really four separate attributes or has something gone wrong in the design? It’s early enough in the morning for me that I’m willing to accede to there being use cases in a well-designed database.

Comments closed

Repetition in R with rep()

Published 2023-06-26 by Kevin Feasel

Steven Sanderson shows off a function in R:

As a programmer, you’re constantly faced with the need to repeat tasks efficiently. Repetition is a fundamental concept in programming, and R provides a powerful tool to accomplish this: the rep() function. In this blog post, we will explore the syntax of the rep() function and delve into several examples to showcase its versatility and practical applications. Whether you’re working with data manipulation, generating sequences, or creating repeated patterns, rep() will become your go-to function for mastering repetition in R.

Read on for the basic syntax, as well as several examples of how to use the rep() function.

Comments closed

Finding the Actual Error Line in sp_executesql

Published 2023-06-23 by Kevin Feasel

Thom Andrews solves a problem:

Notice that the error line states line 2 not line 8, which is the line the sys.sp_executesql was called on. Knowing the line the error occured on within the dynamic batch is certainly important, but if you’re working with multiple dynamic batches you have no way of knowing which dynamic batch might have produced the error; was it the one executed on line 100? Line 200? Line 350?

Solving this problem wasn’t wasn’t exactly simple, and it came with a couple of caveats.

Click through for the approach. As Thom mentions, it isn’t perfect, but it is reasonable and interesting.

Comments closed

Calculating Time Series Differences

Published 2023-06-19 by Kevin Feasel

Steven Sanderson notices the difference:

The diff() function in R calculates the differences between consecutive elements in a vector or a time series. It takes a single argument, which is the input vector, and returns a new vector with the differences. This function is particularly useful for analyzing the rate of change, identifying patterns, and detecting anomalies in your data. 

Read on to see how you can use it, as well as some examples of usage.

Comments closed

The Value of QUOTENAME

Published 2023-05-31 by Kevin Feasel

Quoth Chad Callihan, “Occasionally more”:

QUOTENAME can be used to make sure database objects are valid in your query. Most of the time, objects like table names only contain valid characters, so there’s nothing to worry about. But nobody’s perfect. Let’s look at an example of what can happen when somebody creates a table with a forward slash in the name and see how QUOTENAME can be used to query against it.

QUOTENAME is also a good way of preventing SQL injection, though I still prefer appropriate use of exec sp_executesql in any case in which it’s possible to use.

Comments closed

MATCHBY for DAX Window Functions

Published 2023-05-26 by Kevin Feasel

Jeffrey Wang plays matchmaker:

In this final installment of our four-part mini-series on DAX window functions, we’ll be focusing on a new development. The May release of Power BI Desktop has enriched all DAX window functions – namely OFFSET, WINDOW, INDEX, RANK, and ROWNUMBER – with an additional sub-function, MATCHBY, supplementing the existing sub-functions, ORDERBY and PARTITIONBY. In this article, we’ll delve into the purpose of the MATCHBY function, along with the three specific problems it aims to resolve.

Read on to understand what the MATCHBY sub-function does and why it can be important.

Comments closed

Recursive Common Table Expressions in Snowflake

Published 2023-05-18 by Kevin Feasel

Kevin Wilkie is too fancy for simple joins:

Today, I want to talk about that fun edge case when you’re having to join a table to itself in Snowflake. Does it happen often? Not unless your architect just hates you.

Let’s use the normal pieces of data that everyone uses for this kind of thing – employee/manager relationships. We have our employee table that we’ve been working off that we’ll play with for this example.

The syntax is a bit different from T-SQL, but the concept is still the same.

Comments closed