-1

My end goal is to get e-mail addresses from a database. However, my first query retrieves ID's that correspond to the e-mail addresses in a different table. However, the query returns the ID's in the following way:

1:FOO,2:FOO,3:FOO,...

The way to retrieve the e-mails is to do

SELECT email from emails where id in (SELECT id from other_table)

However, since the IDs from other_table are in the ID:FOO format and the e-mail address table has them without :FOO, I don't get the e-mails back.

I also tried 

SELECT email FROM subscribers where CONCAT(id,":FOO") in (SELECT id from other_table)

However that doesn't work. I think I need to be able to retrieve the ID's from other_table without :FOO to make this work. How can I remove all :FOO's from the results?

db2791
  • 1,040
  • 6
  • 17
  • 30

1 Answers1

0

Use GROUP_CONCAT

SELECT `email` FROM `emails` WHERE `id` IN(
   SELECT REPLACE(GROUP_CONCAT(`id`), ':FOO', '') FROM `other_table`
)
Remco K.
  • 644
  • 4
  • 19
  • 1
    What if some of the fields are `:FOO` and others are `:BAR`? Is there a way to use Regex to match all of them? – db2791 May 16 '17 at 19:27
  • http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql – Remco K. May 16 '17 at 19:29