MySQL stop returning last item in substring more than once

Question

I have the field 'actors' in my database which is a comma separate list of 1 or more text items. but NOT fixed.. it could be any number of items from 1 to 10.

I am using the following code in MYSQL to break the single field into separate columns

SELECT 
substring_index ( substring_index ( `actors`,',',1 ), ',', -1) AS `Name of Actor 1`,
substring_index ( substring_index ( `actors`,',',2 ), ',', -1) AS `Name of Actor 2`,

.... all the way down to ...

substring_index ( substring_index ( `actors`,',',10 ), ',', -1) AS `Name of Actor 10`

and this works great apart from one thing.. when the list runs out, the last item is just repeated over and over

In a list of 3 actors, I need Name of Actor 4 - 10 to be NULL, what actually happens is Actor 4- 10 is filled with Actor 3 (the last item)

How can I expand (if possible) on the SQL statement to stop returning the last item when its already been used.

Possible duplicate of [SQL split values to multiple rows](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) — Prabhat G, Jun 13 '17 at 10:27
Table is inherited from old project, updates are being made as a redesign in in progress for later in the year — MOLEDesign, Jun 15 '17 at 12:29

score 1 · Accepted Answer · answered Jun 13 '17 at 10:26

Try counting the number of commas in the string and if there are enough commas, then there are entries to do your substring. If there is one comma (i.e. if there are more than 0 commas) then there are at least two entries. Don't provide an ELSE to the CASE so the output is null if there are not enough entries:

CASE WHEN LENGTH(`actors`) - LENGTH(REPLACE(`actors`, ',', '')) > 0 THEN
  substring_index ( substring_index ( `actors`,',',2 ), ',', -1) END AS `Name of Actor 2`,
CASE WHEN LENGTH(`actors`) - LENGTH(REPLACE(`actors`, ',', '')) > 1 THEN
  substring_index ( substring_index ( `actors`,',',3 ), ',', -1) END AS `Name of Actor 3`,

And so on

Absolutely beautiful. Works perfectly. Thanks! – MOLEDesign Jun 13 '17 at 11:39 — MOLEDesign, Jun 13 '17 at 11:39

MySQL stop returning last item in substring more than once

1 Answers1