0

the column exportIds of my database table contains an array of string, such as 

["2","3","99"]

The column is of type "varchar(255)".

I would now like to get those rows which do have a specific value in that column array, e.g. "2". First, I tried the following but with no result:

select lastName, exportIds from members where find_in_set("2", exportIds) > 0

Then, I thought the reason could be the surrounding brackets, so I tries a substring approach:

select lastName, substring(exportIds, 2, length(exportIds) -2) from members where find_in_set("2", substring(exportIds, 2, length(exportIds) -2)) > 0

I read somewhere that a cast might be needed, so I tried:

select lastName, substring(exportIds, 2, length(exportIds) -2) from members where find_in_set(cast(2 as char), substring(exportIds, 2, length(exportIds) -2)) > 0

No result either. What am I doing wrong?

Thanks!

AntonSack
  • 1,021
  • 2
  • 25
  • 47
  • 3
    Well, you got your data structure wrong and now you are paying the price. Normalise your data. – Shadow Sep 15 '19 at 12:13
  • As said by Shadow normalization is needed. Apart from that, show us the sample data in the column in a table format. – James Sep 15 '19 at 12:15

0 Answers0