sql - STRING_SPLIT in MySql- how to do it?

Question

SELECT er.pNumber, er.name, ep.fPosition, eo.res
FROM events_shot er, events_shot_final ep, events_shot_final_res eo, events_gear era
WHERE era.idShot=er.idShot AND ep.idPhoto=era.idPhoto AND eo.idShot=era.idShot
    AND era.idShot=42 AND eo.shotType='PRT'
    AND er.pNumber IN (
        SELECT *
        FROM STRING_SPLIT(eo.photosId,'-')
        )

shotsId is a String like 12-1-8-7... with n pNumber id separated by '-'

Unfortunately the query return this error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(eo.photosId,'-') )' at line 7

I can't change the database, how can I change my query?

Any help you can provide would be appreciated.

Answer 1

Fix your data model! Don't store multiple values in a single column.

Learn proper SQL syntax! Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

With that said, you don't need to split the string. You can use string operations:

AND CONCAT('-', eo.photosId, '-') LIKE CONCAT('%-', er.pNumber, '-%')

But you should really start work on fixing the data model as soon as you get this query to work.