In the blacklist table of MySQL there is an email field of type VARCHAR (255). It can be written email-addresses, one or more, separated by commas. Task: check a specific email-address for its presence in the blacklist table.

Suppose we need to check email: test@mail.ru for its presence in the table: Option: SELECT id FROM blacklist WHERE email LIKE '%test@mail.ru%' - will not work, because an address can be entered in the table field, for example 123test@mail.ru - which will falsely work for this request!

Since the email field can contain one or several email addresses separated by commas, the following search may be formulated verbally:

Find the exact entry of the string in the field, taking into account such variants of its location in the field, for example test@mail.ru:

  • field start test@mail.ru field end (one email in the field)
  • beginning of the field test@mail.ru comma or end of the field (email is at the beginning of the field)
  • comma test@mail.ru comma or field end (email is in the middle or end of the field)

How to write such regular expression in MySQL?

  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

As an option to use multiple LIKE

 WHERE email LIKE '%,test@mail.ru,%' OR email LIKE 'test@mail.ru,%' OR email LIKE '%,test@mail.ru'
  • Or maybe there is a construction like email LIKE IN(....) ? Once in SQL regulars I don’t believe .. - Rijen
  • Thank! Query view: SELECT * FROM blacklist WHERE email LIKE 'test@mail.ru' OR email LIKE 'test @ mail.ru,%' OR email LIKE '%, test @ mail.ru' OR email LIKE '%, test @ mail .ru,% 'worked great! - Enshtein
  • one
    It worked, but for some reason it aches in the shower after looking at this code =) Probably it’s bad to do so and it will work slowly .. - Rijen
  • I use for the formation of mailing letters, from 10,000 letters, i.e. I receive 10,000 thousand such requests, distributed over time in a cycle, so far everything is working well without any complaints. The code is not perfect, it would be better to apply the regulars probably, but it doesn’t come to the head of a good elegant solution) - Enshtein
  • @Enshtein, with the scheme you specified, you can hardly make a quick-running query. I suggest you store email addresses in a separate table and associate it with a one-to-many relationship with the blacklist table. This will allow you to make samples more optimal and faster. Demo - db-fiddle.com/f/jCMrR7gBvirBKUkFmew53i/1 - Jigius