Which mysql function can split a string of words in pairs separated by commas?
name1 name2 name3 name4 name5 name6…. this is one string
the output am expecting should be name1 name2 , name3 name4 , name5 name6 ,….
SELECT COLUMN-NAME,
CASE WHEN (LENGTH(COLUMN-NAME) – LENGTH(REPLACE(COLUMN-NAME, ' ', "))+1) <= 2 THEN substring_index ( COLUMN-NAME,' ',2 ) WHEN (LENGTH(COLUMN-NAME) - LENGTH(REPLACE(COLUMN-NAME, ' ', ''))+1) =4 THEN concat( substring_index ( COLUMN-NAME,' ',2 ),'-',substring_index ( substring_index ( COLUMN-NAME,' ',4 ), ' ', -2)) substring_index ( substring_index ( COLUMN-NAME,' ',4 ), ' ', -2) WHEN (LENGTH(COLUMN-NAME) - LENGTH(REPLACE(COLUMN-NAME, ' ', ''))+1) =6 THEN concat(substring_index ( COLUMN-NAME,' ',2 ),' , ',substring_index ( substring_index ( COLUMN-NAME,' ',4 ), ' ', -2),' , ',substring_index ( substring_index ( COLUMN-NAME,' ',6 ), ' ', -2)) substring_index ( substring_index ( COLUMN-NAME,' ',6 ), ' ', -2)
WHEN
(LENGTH(COLUMN-NAME) – LENGTH(REPLACE(COLUMN-NAME, ' ', "))+1) =8
THEN
concat(substring_index ( COLUMN-NAME,' ',2 ),' , ',substring_index ( substring_index ( COLUMN-NAME,' ',4 ), ' ', -2),' , ',substring_index ( substring_index ( COLUMN-NAME,' ',6 ), ' ', -2),' , 'substring_index ( substring_index ( COLUMN-NAME,' ',8 ), ' ', -2))
substring_index ( substring_index ( COLUMN-NAME,' ',8 ), ' ', -2)
WHEN
(LENGTH(COLUMN-NAME) – LENGTH(REPLACE(COLUMN-NAME, ' ', "))+1) =10
THEN
concat(substring_index ( COLUMN-NAME,' ',2 ),' , ',substring_index ( substring_index ( COLUMN-NAME,' ',4 ), ' ', -2),' , ',substring_index ( substring_index ( COLUMN-NAME,' ',6 ), ' ', -2),' , ',substring_index ( substring_index ( COLUMN-NAME,' ',8 ), ' ', -2),' , ',substring_index ( substring_index ( COLUMN-NAME,' ',10 ), ' ', -2))
END AS Col,
(LENGTH(COLUMN-NAME) – LENGTH(REPLACE(COLUMN-NAME, ' ', "))+1) AS Number of words
FROM TABLE-NAME;
Is there a shorter way ?