-1
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.

Diego
  • 3
  • 2
  • 4
    That function is used in SQL Server 2016, not in MySQL. – Barbaros Özhan Nov 22 '18 at 10:03
  • eo.combination does not appear in the code above! Are you sure it's the same code? But yes STRING_SPLIT does not exist in mysql. [Here's a list of mySQL string functions you can use](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html) – ADyson Nov 22 '18 at 10:03
  • You're using a SQL Server function in MySQL, and you're giving an error message from a different query. In coding of any kind, attention to detail pays vast rewards. – MatBailie Nov 22 '18 at 10:05
  • 2
    If you have any control, change the database design. Putting multiple values in a delimited string breaches all sorts of SQL principles. The correct design is to have a separate row for each value *(search for normal form)*. Then you don't have to mess around with splitting strings, and you can make use of indexes and constraints *(plus 1001 other benefits)*. – MatBailie Nov 22 '18 at 10:08
  • 2
    (Unrelated) Switch to modern, explicit `JOIN` syntax everywhere! Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed. – jarlh Nov 22 '18 at 10:13
  • guys I can't change the database. How can I change my query? – Diego Nov 22 '18 at 10:16
  • @ADyson sorry i did a mistake when I reported the Error. But how can I change my query? Do you know a solution with mysql functions? – Diego Nov 22 '18 at 10:32
  • Possible duplicate of [Can you split/explode a field in a MySQL query?](https://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query) – GSerg Nov 22 '18 at 10:47
  • https://stackoverflow.com/questions/53429324/string-split-in-mysql-how-to-do-it – jarlh Nov 22 '18 at 10:56
  • @Diego "Do you know a solution with mysql functions?"...I gave you a link to all the mysql string functions, so you can read about them and decide which one to use. Didn't you look at it? – ADyson Nov 22 '18 at 11:05

2 Answers2

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
Joakim Danielson
  • 43,251
  • 5
  • 22
  • 52
  • @Diego Of all comments and answers, you chose the wrong one. This will return a match regardless of the `-` and other characters. It will match `56` in `123-456-789`. It will match `56` in `123 456 789`. It will match `56` in `123456789`. – GSerg Nov 22 '18 at 12:25
  • @GSerg thank you, unfortunately I can't test the code in this moment, and I must find a solution as soon as possible – Diego Nov 22 '18 at 12:40
  • @Diego I have added a second version that is more exact and you don't need to change your data for it to work properly. – Joakim Danielson Nov 22 '18 at 12:41
  • @JoakimDanielson MySql [does not recognize `\d`](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-syntax). So your expression still matches `56` in `123456789`. – GSerg Nov 22 '18 at 12:49
  • @GSerg, sloppy of me. Fixed. – Joakim Danielson Nov 22 '18 at 12:52
0

You have a bad database structure, eo.photosId should be a separate table.

But you have an extremely suboptimal solution without changing the DB structure:

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

you must change 12-1-8-7 to -12-1-8-7- and find ID with delimiter -1- + % - LIKE any char operator

bato3
  • 2,695
  • 1
  • 18
  • 26