Breaking out a string

DMJerry · June 2018

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

FreeSoul · June 2018

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 data

Thanks,

FrSol

ST_Superman · June 2018

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.

user084060 · January 2021

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

jaeW_at_Onyx · January 2021

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.

Breaking out a string

Comments

Categories