extracting information from the "service"

[dfoliveira]

Hello,

I have a doubt about extracting information from the "service" table. I need to extract the service and subservice, as in the example below, but I don't know how to do it.

[skullz]

Code: Select all

SELECT name AS 'Full Service Name', 

SUBSTRING_INDEX(name,'::',1) AS 'Main Service',

CASE WHEN LENGTH(name)-LENGTH(REPLACE(name, '::', '')) >= 2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(name,'::',2),'::',-1) ELSE NULL END AS 'Sub Service 1',

CASE WHEN LENGTH(name)-LENGTH(REPLACE(name, '::', '')) >= 4 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(name,'::',3),'::',-1) ELSE NULL END AS 'Sub Service 2',

CASE WHEN LENGTH(name)-LENGTH(REPLACE(name, '::', '')) >= 6 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(name,'::',4),'::',-1) ELSE NULL END AS 'Sub Service 3'

FROM service

good luck

[dfoliveira]

Thank you, perfect.

Thank you very much