1

This is a common question, but I found my problem to be unique. I have a database in MySQL workbench that has multiple order number (inconsistent length) and status conditions (three types) in a single column. I must separate the order numbers from their status and the order numbers from each other.

What I have:

|NUMBER_STATUS|

|1234-START, 12323-END|
|234 - END, 12423-START, 53443-WIP|

What my final output should be: 

|Number_STATUS_1|   |Number_STATUS_2|    |Number_STATUS_3|
      1234               12323  
       234               12423                 53443

There are over 25,000 data points. So far, I have tried writing a left function:

SELECT *,
    LEFT(NUMBER_STATUS, locate('-START', NUMBER_STATUS)-1) AS 'NUMBER_STATUS_1'
FROM Request;

This function does exactly what I want: Creates a new column with the status removed, but does not carry over any other data in its row.

I thought of three plans to attack this:

  1. create new columns split the original by pieces. I can do this in excel using "split text into cells" and then bring it into MySQL Workbench, but I know SQL is more powerful, so I would like to write a script for this.

  2. Create a pseudo table that stores each new column (Number status 1, number status 2, etc) but the data changes daily so I don't want to limit the number of new columns that can be created.

  3. Ask you all.

Some other links I referenced for help:

Split one column to multiple columns but data will vary SQL

Split string and return data in multiple columns

But my knowledge of SQL is still growing and I have no idea what these functions mean, I would greatly appreciate the help.

Community
  • 1
  • 1
redracer67
  • 21
  • 1
  • 6
  • 2
    The best answer is not to design your table like this. You should normalize your schema. – Barmar Mar 10 '14 at 23:40
  • FYI: The other questions you referenced seem to refer to SQLServer rather than MySQL, so they may not apply directly. – dg99 Mar 10 '14 at 23:41
  • 1
    Create three `CASE WHEN` clauses to handle each status type, assuming that there are only three possible status values. – PM 77-1 Mar 11 '14 at 00:10
  • First of all--thank you for the help so far! @Barmar : I do not have a choice in how the data is designed. I will be extracting the data from an online database and the schema is already set in stone. I am in fact trying to fix it and this is one of my first steps. @ dg99, yes, that was my understanding. Functions such as "locate" would not exist in a SQLserver and even within MySQL, some functions are different when using MySQL workbench. I do not have a choice in what SQL program I should use. @ PM77-1: Could you please give me an example of the CASE WHEN function? – redracer67 Mar 12 '14 at 00:11
  • @user3294219 If you want dg99 and PM77-1 to see your messages, you need to post them in separate comments. – Barmar Mar 12 '14 at 04:09

1 Answers1

0

This is what I used to solve my problem: I altered table and column names for personal reasons. I hope this helps other people in the future! 

/*remove status name from data*/

Update table
Set
            columnSTATUS = REPLACE(columnSTATUS, '-status1', '');

Update table
Set
                columnSTATUS = REPLACE(columnSTATUS, '-staus2', '');

Update table
Set
                columnSTATUS = REPLACE(columnSTATUS, '-status3', '');

    /*split data into columns*/
    UPDATE table SET
        `column1` = IF ( 
            LOCATE(',', columnSTATUS) >0,
            SUBSTRING(columnSTATUS, 1,LOCATE(',', columnSTATUS)-1),
            column_STATUS
    ),
        `column2` = IF( 
            LOCATE(',', columnSTATUS) > 0,
            SUBSTRING(columnSTATUS, LOCATE(',', columnSTATUS)+1),
            ''); 
    UPDATE table SET
        `column3` = IF (
            LOCATE(',', column2) >0, 
            SUBSTRING(column2, LOCATE(',', column2)+1), '');
    UPDATE table SET
        `column4` = IF (
            LOCATE(',', column3) >0, 
            SUBSTRING(column3, LOCATE(',', column3)+1), '');

    /*remove data remaining in column after commas*/
    UPDATE table SET 
        column2 = SUBSTRING_INDEX(column2, ',', 1);
    UPDATE Service_Request SET
        column3 = SUBSTRING_INDEX(column3,',',1);


NB: I do not have a choice how the data is imported.
redracer67
  • 21
  • 1
  • 6