Which mysql function can split a string of words in pairs separated by commas?

donbwilliam 2016-07-29 07:22:15

Help me with a function that can split a string of words e.g
name1 name2 name3 name4 name5 name6…. this is one string
the output am expecting should be name1 name2 , name3 name4 , name5 name6 ,….
Aaron Bertrand 2016-07-29 17:17:50
This site is geared toward SQL Server. Have you looked at this, though? https://blog.fedecarg.com/2009/02/22/mysql-split-string-function/
donbwilliam 2016-07-29 09:51:40
/********Splits a string with commas***********/
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 ?

July 29, 2016
No Comments