Extracting Specific Numbers from a String in SQL

Discover how to effectively extract `pack sizes` from product descriptions in SQL using regular expressions in Snowflake.
---

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: How to extract Specific Numbers from String in SQL

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Extracting Specific Numbers from a String in SQL: A Step-by-Step Guide

When working with datasets containing product descriptions, a common challenge arises: how do you extract specific numerical values embedded within those strings? This problem is especially pertinent when the format of the descriptions can vary significantly. For instance, you might have product descriptions like "PRODUCT A 3 CHEESE SLICE 170 GM" and need to isolate the quantity representation, such as "170". In this guide, we’ll demonstrate how to tackle this issue using SQL and regular expressions, specifically in Snowflake.

Understanding the Challenge

In the example below, we have a set of product descriptions from which we want to extract the expected pack sizes. The table illustrates the input strings alongside the output we wish to achieve.

Sample Data

PRODUCT_DESCRIPTIONEXPECTED PACK SIZECURRENT_RESULTPRODUCT A 3 CHEESE SLICE 170 GM1703170PRODUCT B SUGAR 1.3KG (CL)130013PRODUCT C CHEESE SLICES 12X156GM15612156PRODUCT KETCHUP BOTTLE 200GM (CL)200200PRODUCT KETCHUP 1.3KG (CL)130013KITCHEN 88 KALE & CHIA BASMATI RICE 150GM15088Despite using a method that captures all numerical literals from the description, the results did not meet the expected outputs. Let’s delve into a more effective solution.

The SQL Solution

To successfully extract specific pack sizes from the strings, we can utilize the REGEXP_SUBSTR function in Snowflake SQL. Here's how you can do it:

Step-by-Step Explanation of the Code

Select Product Description: Start by selecting the column containing your product descriptions.

Extracting Pack Size Number: Use REGEXP_SUBSTR to find the number associated with the pack size, filtering for units like "KG" and "GM".

Determining the Unit: Extract the unit of measurement (e.g. KG, GM) using another REGEXP_SUBSTR call.

Converting to Consistent Units: Use the IFF function to convert any kilograms to grams for consistency in output.

Return Results: The final selection will group the desired output together for readability.

Example SQL Code

Here’s a working example that implements the steps laid out above:

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

Interpreting the Output

The result of this SQL query would return the product descriptions along with their corresponding pack sizes as follows:

PRODUCT_DESCRIPTIONPACKSIZE_NUMPACKSIZE_UNITPACKSIZEPRODUCT A 3 CHEESE SLICE 170 GM170GM170PRODUCT B SUGAR 1.3KG (CL)1.3KG1300PRODUCT C CHEESE SLICES 12X156GM156GM156PRODUCT KETCHUP BOTTLE 200GM (CL)200GM200PRODUCT KETCHUP 1.3KG (CL)1.3KG1300Conclusion

Extracting specific numbers from strings in SQL can initially seem daunting, but using regular expressions simplifies the process significantly. With the specified SQL code, you can accurately pinpoint pack sizes from diverse product descriptions, enabling better data handling and analysis.

If you have further questions or need clarification on any of the steps outlined here, feel free to reach out! Happy querying!