Search code examples
mysqlsql-like

mySQL: use LIKE with SUBSTR in a query?

How can I do something like this:

SELECT * FROM .... WHERE wp_users.user_email LIKE '%SUBSTR(wp_callbacks.post_data, INSTR(wp_callbacks.post_data, 'payer_email')+19, 20)%'...

What I am trying to do is query a wp_callbacks table (that stores it in the format like s:11:"payer_email";s:18:"[email protected]" ...) and see if the actual email from wp_users matches that email from wp_callbacks table.

The problem is that I don't know how long the email itself is so I picked a random value (20) in SUBSTR call to grab the first 20 characters of the email itself and then I want to check if the user_email is LIKE '%...%' that value.

Ideas?

Solution

  • How about - not tried though

    SELECT * 
FROM .... 
WHERE wp_users.user_email LIKE 
    CONCAT('%', 
    SUBSTR(wp_callbacks.post_data, INSTR(wp_callbacks.post_data, 'payer_email')+19, 20),
    '%');