Breaking out a string
Hi,
I have product information that is a string seperated by commas. I wanted to take the first line and convert it to the lines in bold and continue the pattern. How can I accomplish this:
Style # | Selling Seasons |
M200B14BAR | 14B ,15D ,14C ,14D ,15A ,15B ,15C ,16A ,16B ,16C ,16D ,17A ,17B ,17C ,17D ,18A ,18B |
M200B14BAR | 14B |
M200B14BAR | 15D |
Comments
-
Hi DMJerry,
I am not sure whether this would help or not - but eh, worth a shot. You may be able to use the SPLIT_PART function in RedShift SQL transform if number of 'selling seasons' are fixed. And then too, it will split in columns rather than rows - but you can than later transform it easily.
Code Snippet:
SELECT data.style
, data.selling_seasons
, SPLIT_PART(data.selling_seasons, ',', 1), SPLIT_PART(data.selling_seasons, ',', 2)
...
, SPLIT_PART(data.selling_seasons, ',', 17)
FROM dataThanks,
FrSol
0 -
Here is an option to get this done in MySQL. This will take several transforms. This will allow up to 30 selling seasons. If you need more, you can add more to the first select statement.
1.)
SELECT
`Style #`,
@num_seasons := 1 + LENGTH(`Selling Seasons`) - LENGTH(REPLACE(`Selling Seasons`, ' ,', ' ')) AS num_seasons,
SUBSTRING_INDEX(`Selling Seasons`, ' ,', 1) AS season1,
IF(@num_seasons > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 2), ' ,', -1), '') AS season2,
IF(@num_seasons > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 3), ' ,', -1), '') AS season3,
IF(@num_seasons > 3, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 4), ' ,', -1), '') AS season4,
IF(@num_seasons > 4, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 5), ' ,', -1), '') AS season5,
IF(@num_seasons > 5, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 6), ' ,', -1), '') AS season6,
IF(@num_seasons > 6, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 7), ' ,', -1), '') AS season7,
IF(@num_seasons > 7, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 8), ' ,', -1), '') AS season8,
IF(@num_seasons > 8, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 9), ' ,', -1), '') AS season9,
IF(@num_seasons > 9, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 10), ' ,', -1), '') AS season10,
IF(@num_seasons > 10, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 11), ' ,', -1), '') AS season11,
IF(@num_seasons > 11, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 12), ' ,', -1), '') AS season12,
IF(@num_seasons > 12, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 13), ' ,', -1), '') AS season13,
IF(@num_seasons > 13, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 14), ' ,', -1), '') AS season14,
IF(@num_seasons > 14, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 15), ' ,', -1), '') AS season15,
IF(@num_seasons > 15, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 16), ' ,', -1), '') AS season16,
IF(@num_seasons > 16, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 17), ' ,', -1), '') AS season17,
IF(@num_seasons > 17, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 18), ' ,', -1), '') AS season18,
IF(@num_seasons > 18, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 19), ' ,', -1), '') AS season19,
IF(@num_seasons > 19, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 20), ' ,', -1), '') AS season20,
IF(@num_seasons > 20, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 21), ' ,', -1), '') AS season21,
IF(@num_seasons > 21, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 22), ' ,', -1), '') AS season22,
IF(@num_seasons > 22, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 23), ' ,', -1), '') AS season23,
IF(@num_seasons > 23, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 24), ' ,', -1), '') AS season24,
IF(@num_seasons > 24, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 25), ' ,', -1), '') AS season25,
IF(@num_seasons > 25, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 26), ' ,', -1), '') AS season26,
IF(@num_seasons > 26, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 27), ' ,', -1), '') AS season27,
IF(@num_seasons > 27, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 28), ' ,', -1), '') AS season28,
IF(@num_seasons > 28, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 29), ' ,', -1), '') AS season29,
IF(@num_seasons > 29, SUBSTRING_INDEX(SUBSTRING_INDEX(`Selling Seasons`, ' ,', 30), ' ,', -1), '') AS season30
FROM dojo_dmjerry-- Generate Output Table num_seasons
We then need to transpose all of the seasons into a single column over the next 4 transforms.
2.)
SELECT
group_concat(
concat(
'Select `Style #`,','`', COLUMN_NAME ,'` as `Selling Season`', 'FROM num_seasons')
SEPARATOR ' UNION ALL ') as prep_table
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='num_seasons'
AND COLUMN_NAME NOT IN('num_seasons','Style #')-- Generate Output Table prep_table
3.)
DROP PROCEDURE IF EXISTS transpose;
4.)
CREATE PROCEDURE transpose()
BEGIN
DROP TABLE IF EXISTS selling_seasons;SELECT prep_table into @sql2 FROM prep_table;
SET @str=concat('create table selling_seasons as ',@sql2);
PREPARE q from @str;
EXECUTE q;END
5.)
CALL transpose;
6.)
SELECT * from selling_seasons WHERE `Selling Season` <> ''
ORDER BY 1,2-- Generate Output Table output
Then you can use
SELECT * FROM output
as your Output DataSet.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
Any Idea why this error might come up?
The database reported a syntax error: FUNCTION SPLIT_PART does not exist
I used exactly the same split_part logic
0 -
flick through this video. might get you there more easily.
https://www.youtube.com/watch?v=oYcpYE7DiV4
in a nutshell, count the number of commas in your string.
duplicate the data for 1+n commas
for the nth copy of the row, keep the data after the nth comma.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive