SQL - Search cell delimited by carriage_return
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

This image describes my SQL problem:

http://i.imgur.com/b71anFM.gif

I have a standard database where the owner enters in "email aliases" into a TEXT field - directly in PHPMyAdmin.

I just need to search this "email alias" field, and see if the field contains a given email address.

I could use a query like:

SELECT * FROM table WHERE emailalias LIKE % $_GET["email"] %

The problem with this method is that, notice that the string alf@gmail.com exists in the emailalias cell for both users.

So how do i return only the CORRECT user in this case?

awarded to kerncy
Tags
MySQL

Crowdsource coding tasks.

1 Solution

Winning solution

this should do the work :

SELECT * FROM table WHERE emailalias LIKE  "%\n$_GET["email"]\n%" or emailalias like "$_GET["email"]\n%" or emailalias LIKE "%\n$_GET["email"]"

Test carriage return with \n and test also first and last line which are not surrounded by \n.

Updated solution as in comment :

SELECT * 
FROM table
WHERE CONCAT('=', REPLACE(REPLACE(emailalias, CHAR(10), '='), CHAR(13), '='), '=') LIKE  "%=alf@gmail.com=%"

I tested this one in phpmyadmin, it works with your test case.

I dont think this will work. Because this condition "$_GET["email"]\n%" is fulfilled in both rows in the sample image. (for alf@gmail.com) But its important that the query only return one row.
tonloc over 2 years ago
I don't think so as the % char is not set before the $_GET so it will match for the first line only, that contains no preceding chars.
kerncy over 2 years ago
Thanks. I'm running it, but can't seem to get it to return any results. I think maybe the string is confused about the #13 and #10 carriage_returns... not sure ; (
tonloc over 2 years ago
maybe something like select * from table where concat('=',replace(replace(emailalias, char(10), '='), char(13), '='),'=') like "%=$_GET["email"]=%" this will transform char 10 and 13 by "=" to match email surrounded by "="
kerncy over 2 years ago
cool thanks!
tonloc over 2 years ago