How to Split Strings in SQL and Create a View Without Using Functions

Learn how to split strings from a SQL column into multiple columns for better data visualization, all without needing to create functions!
---
This video is based on the question https://stackoverflow.com/q/72704760/ asked by the user 'Alex' ( https://stackoverflow.com/u/14463139/ ) and on the answer https://stackoverflow.com/a/72705322/ provided by the user 'Greg Pavlik' ( https://stackoverflow.com/u/12756381/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

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 create view by splitting string from column to columns (without using create function) in SQL

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Creating Views from Split Strings in SQL

Data manipulation is a fundamental aspect of working with databases, especially when it comes to preparing datasets for analysis. One common challenge that SQL users encounter is the need to split a concatenated string from a single column into multiple columns. This guide will walk you through a specific example using SQL to transform data from a products table.

Understanding the Challenge

Imagine we have a table named products with the following structure:

idraw132Description: mangoes; Cost: 15-30; Saving: 64; ER: E 51; EnR: D 56; Eligible: Y$ $ Description: oranges; Cost: 1,000-1,400; ...497Description: bananas; Cost: 25; Saving: 9; ER: E 52; EnR: D 56; Eligible: N$ $ Description: apples; Cost: 4,000-14,000; ...In this dataset, the raw column contains multiple product descriptions, each consisting of several attributes mixed together and separated by specific delimiters (such as $ $ and ;). Our goal is to separate this concatenated data into distinct columns such as Description, Cost, Saving, etc., in a new view format.

Expected Output

The desired final structure for our data should look like this:

idDescriptionCostSavingEREnREligible132mangoes15-3064E 51D 56Y132oranges1,000-1,40041C 75D 58Y497bananas259E 52D 56N497apples4,000-14,000165E 47E 53Y497pears800-1,200393C 73D 55YStep-by-Step Solution

The approach to achieve the desired result can be broken down into the following steps:

Step 1: Split Strings into Rows

To split the raw string into separate rows based on the $ $ delimiter, we can use a lateral join combined with the split_to_table table function. Here’s how this can be written in SQL:

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

This SQL statement will create multiple rows for each product where each row contains a piece of the raw column.

Step 2: Extract Attributes into Columns

Next, we need to split each of these rows (which still contain semi-colon-separated values) into individual attributes. We can utilize JSON parsing in SQL to convert these strings into key-value pairs.

Here’s a critical SQL query that shows the entire transformation:

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

This SQL snippet demonstrates how to:

Use WITH clause to create a Common Table Expression (CTE) that processes the split values.

Parse the string values from the VALUE column into a JSON object, thereby cleanly allocating them into relevant columns.

Optional Step: Create a User-Defined Function

While the initial requirement was to avoid creating a function, consider the benefit of one for better readability and performance. A User-Defined Function (UDF) can turn each separated string into a JavaScript object efficiently.

Here’s an example definition of such a function:

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

You can then modify your SELECT statement to utilize this function easily.

Conclusion

Transforming a concatenated string into multiple columns in SQL is a multi-step process that leverages splitting and parsing techniques. By using lateral joins, string manipulation, and JSON handling, you can achieve a clean and structured format suitable for analysis.

For those who want cleaner and more manageable solutions, consider using User-Defined Functions, despite the initial requirement to avoid them. Each approach has its advantages and can greatly enhance your SQL data manipulation capabilities.

Now you can tackle similar challenges in your SQL queries with confidence!

Видео How to Split Strings in SQL and Create a View Without Using Functions канала vlogize