mysql splits fields according to separators

  1. Although there are many similar ways of writing, I'll write it myself. Some separators are not fixed in the number of columns. How can I split multiple columns according to separators
insert into tpems.sp_questions_bank (id, title, option_a, option_b, option_c, option_d, option_e, answer1, answer2, parse, qtpye, diff, md5, subjectId, gradeId, knowledges, area, year, paperTpye, source, fromSite, isSub, isNormal, isKonw, tiid, Similarity, isunique, md52, s_type, s_qid, s_pid, s_user_id, s_user_name, s_option_number, s_status, s_create_time, s_last_update_time, s_is_exercise, s_select_number
)
SELECT 
    null,
    t.title,
    IF(t.opi >= 1, t.option_a, '') a,
    IF(t.opi >= 2, t.option_b, '') b,
    IF(t.opi >= 3, t.option_c, '') c,
    IF(t.opi >= 4, t.option_d, '') d,
    IF(t.opi >= 5, t.option_e, '') e,
    t.answer,
    null,
    t.description, 
    t.type,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    0,
    t.id,
    t.pid,
    t.user_id,
    t.user_name,
    t.option_number,
    t.status,
    t.create_time,
    t.last_update_time,
    t.is_exercise,
    t.select_number
FROM
    (SELECT 
            sp.id,
            sp.user_id,
            sp.type,
            sp.title,
            sp.user_name,
            sp.option_number,
            sp.answer,
            sp.description,
            sp.status,
            sp.create_time,
            sp.last_update_time,
            sp.is_exercise,
            sp.pid,
            sp.select_number,
            (LENGTH(sp.option) - LENGTH(REPLACE(sp.option, '^^', ''))) / 2 + 1 AS opi,
            if(sp.type in(4,5,6),sp.option,SUBSTRING_INDEX(SUBSTRING_INDEX(sp.option, '^^', 1), '^^', - 1)) AS option_a,
            if(sp.type in(4,5,6),'',SUBSTRING_INDEX(SUBSTRING_INDEX(sp.option, '^^', 2), '^^', - 1)) as option_b,
            if(sp.type in(4,5,6),'',SUBSTRING_INDEX(SUBSTRING_INDEX(sp.option, '^^', 3), '^^', - 1)) as option_c,
            if(sp.type in(4,5,6),'',SUBSTRING_INDEX(SUBSTRING_INDEX(sp.option, '^^', 4), '^^', - 1)) as option_d,
            if(sp.type in(4,5,6),'',SUBSTRING_INDEX(SUBSTRING_INDEX(sp.option, '^^', 5), '^^', - 1)) as option_e
    FROM
        tpems.sp_question sp) t;

First, describe my sql work scenario. There is a column of data separated by the separator "^ ^". The value of a field needs to be separated and put into different option s. There are five option s, and then the number of separators in the field is not fixed. Here, I've determined the maximum upper limit value. I can only write 5 at most. Then it's easy.

(LENGTH(sp.option) - LENGTH(REPLACE(sp.option, '^^', ''))) / 2 + 1 AS opi

Determines the number of columns that can be distinguished by the separator.

Then determine the number of options

    IF(t.opi >= 1, t.option_a, '') a,
    IF(t.opi >= 2, t.option_b, '') b,
    IF(t.opi >= 3, t.option_c, '') c,
    IF(t.opi >= 4, t.option_d, '') d,
    IF(t.opi >= 5, t.option_e, '') e,

If function, if (expression, a,b) Expression returns a if true, b otherwise So the judgment here is that if there are only two option s, then the values of 3, 4 and 5 are empty To successfully expand a column of values to multiple columns

Tags: SQL

Posted on Thu, 07 Nov 2019 09:37:10 -0800 by dfwcomputer