mysql - Error Code: 1064. You have an error in your SQL syntax - STRING_SPLIT

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

Any help you can provide would be appreciated.

Answer 1

Try to match using a regex rather than splitting a string, replace the code that gives you an error with

AND REGEXP_INSTR(eo.photosId, CONCAT('-?', er.pNumber, '-?')) > 0

Update

Instead of trying to match dash-number-dash the below regex matches not number-number-not number to match only the exact number given

AND REGEXP_INSTR(eo.photosId, CONCAT('(^|[^0-9])', er.pNumber, '([^0-9]|$)')) > 0