0

So specifically, I want the index of first integer that is not -1. The comma separated string contains positive integers from 01 to 24, and there are no trailing -1's.

examples: 

02,-1,12 = possible, answer is 1
-1,-1,04 = possible, answer is 3
-1,23,-1 = not possible, no trailing -1's

Extra constraint we can assume for a less generic solution if it helps, maximum/always three integers in the string

I've tried regexes but since mysql only allows matching with regex not something like first index of regex, i can only write a lot of cases (one for every possibility for a fixed integer count of three) but that seems unclean.

Another thing i tried was using the first and last index of -1 since mysql has instr, but can't seem to come up with a solution where only these two are enough to find the answer.

The MySQL version is 5.7

Anil Kumar
  • 127
  • 8
  • 1
    This is why it is bad to store comma separated values, or more than one value in any form, in a single field. You're probably better off handling it client side; or you could make a stored function that takes a string and parses the comma separated values. Since it is a fixed number of values, and therefore commas, you could try finding the first and last indexes of the commas, and use those to parse out the values in complicated CASE expression. – Uueerdo Sep 09 '19 at 16:51
  • @Uueerdo The bad practices involved in this problem are not lost on me, but it's legacy reporting codebase reading queries from a yml. While undoubtedly this is something which should be done on client side, we would have to make major changes to what the particular project currently allows to start writing logic there. – Anil Kumar Sep 09 '19 at 17:02
  • "can only write a lot of cases" - There are "only" three cases + one "not possible" – Paul Spiegel Sep 09 '19 at 17:06
  • http://www.sqlfiddle.com/#!9/584c0dd/22 ;) – Grzegorz Skibinski Sep 09 '19 at 17:20

0 Answers0