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
M200B14BAR14B ,15D ,14C ,14D ,15A ,15B ,15C ,16A ,16B ,16C ,16D ,17A ,17B ,17C ,17D ,18A ,18B
M200B14BAR14B
M200B14BAR15D

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 data

     

    Thanks,

    FrSol

  • 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.” -Superman
  • 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

  • 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"