How to Extract Invoice Numbers from Strings in Snowflake SQL

Learn how to efficiently extract 6-digit invoice numbers from strings and handle multiple invoices in Snowflake SQL.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Extract digits from string using snowflake

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Extracting Invoice Numbers from Strings in Snowflake SQL

When dealing with datasets that contain strings mixed with various types of data, extracting specific information can often be challenging. One common scenario arises when you need to extract invoice numbers from a string where the invoice numbers are always represented as 6-digit sequences. If you've encountered strings like these, you're not alone, and this guide will guide you through a solution using Snowflake SQL.

The Challenge

Imagine you have a dataset with strings containing invoice numbers mixed with other text. Here are a few examples of such strings:

"some text here 123456 some text here"

"Two invoices 124356 and 235478 and some products 6783 and 45639"

"inv -430203 and -404039. some text here"

The main hurdle is that you want to:

Extract only the 6-digit invoice numbers.

Identify cases with multiple invoices and return a placeholder text instead of the actual numbers.

The Ideal Output

As a goal, you may want results that distinctly show the first invoice and indicate multiple invoices clearly. Here’s how that might look:

123456

123457

Multiple Invoices

Alternatively, displaying both invoices in separate columns could be even more beneficial:

Inv 1Inv 2123456124356235478The Solution

To achieve this task, we can leverage the power of Regular Expressions (regex) in Snowflake SQL. Let’s break down the approach step by step.

Step 1: Using REGEXP_SUBSTR

The REGEXP_SUBSTR function allows us to extract substrings that match a specific regular expression. The regex pattern we'll use here is \b\d{6}\b which matches any 6-digit number.

\b asserts a word boundary, ensuring we only capture standalone 6-digit sequences.

\d{6} matches exactly six digits.

Step 2: Building the Query

The SQL query can be structured as follows:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query:

REGEXP_SUBSTR(memo, '\b\d{6}\b', 1, 2): This checks for the second occurrence of a 6-digit number in the string.

IS NULL: If no second invoice is found, it returns the first invoice found.

ELSE 'Multiple Invoices': If a second invoice exists, it flags this case by returning a predefined string.

Conclusion

Using a regex-based SQL query in Snowflake allows you to effectively extract 6-digit invoice numbers from strings while handling scenarios with multiple invoices. By implementing the described REGEXP_SUBSTR function, you can achieve clear and structured results, enhancing the usability of your dataset.

Now that you have this knowledge, you can confidently process your string data and extract the invoices you need. Don't hesitate to experiment with the query to fit your particular dataset's needs. Happy querying!