0

table 1 has two columns: ID and details. in the details column there is a string in this format:

ID1:code1,ID2:code2,...
for example:
ABC:110,ERTL:780,POV:555

now I need to extract the IDs mentioned in details column and search the table for rows with matching IDs:

SELECT * FROM table1 WHERE ID IN (<the IDs mentioned in the details column of first row>)

how to run such query in MySQL 8?

Soheil
  • 587
  • 4
  • 20
  • 1
    What do you mean by the 'first row'? First ordered by what criterion? – Nick Bailey Feb 15 '22 at 19:02
  • 1
    Also worth noting - if you're in control of this database schema (I assume you're not, but you might be) it would make way more sense to just normalize your data rather than use a non-standard string format to store key-value pairs. – Nick Bailey Feb 15 '22 at 19:04
  • @NickBailey by first row I mean "ORDER BY ID DESC LIMIT 1" – Soheil Feb 15 '22 at 19:43
  • 1
    So you can do this in pure MySQL, but it will be ugly and awful and require you do something like this https://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql. If at all possible I recommend you query the database, do the string manipulation in another language and then query with a simple IN query. – Nick Bailey Feb 15 '22 at 20:04
  • I tried this `SELECT * FROM table1 WHERE ID IN (SELECT concat("('",REPLACE(REGEXP_REPLACE(details, ":[0-9]+", ""),",","','"),"')") FROM table1 ORDER BY ID DESC LIMIT 1)`, but it returns empty result. is it wrong? – Soheil Feb 15 '22 at 20:11
  • Convert your value to JSON object (2 REPLACE and CONCAT) then parse to separate values (JSON_KEYS, JSON_TABLE). – Akina Feb 15 '22 at 21:06

0 Answers0