How to separate column content in mysql?-CodePudding

I have this mysql table:

QA_CONTACT_NAME
'ARAJKONWAR'
'BOONKUAN/CHEECHENG'
'CHEECHENGLAM/KFC'

What I want to do is to create another column to separate the one with '/' like this with the left column duplicated:

QA_CONTACT_NAME, QA_EMAIL
'ARAJKONWAR', 'ARAJKONWAR'
'BOONKUAN/CHEECHENG','BOONKUAN'
'BOONKUAN/CHEECHENG','CHEECHENG'
'CHEECHENGLAM/KFC','CHEECHENGLAM'
'CHEECHENGLAM/KFC','KFC'

Any help is appreciated thanks!

mysql query to get the first table:

SELECT DISTINCT(QA_CONTACT_NAME) FROM table1;

CodePudding user response：

To produce an additional row, you need to join to say if you should be looking at the left or right:

select
    QA_CONTACT_NAME,
    substring_index(QA_CONTACT_NAME,'/',if(left_side,1,-1)) QA_EMAIL
from (select 1 left_side union all select 0) left_or_right
join yourtablename on instr(QA_CONTACT_NAME,'/') or left_side

fiddle