How to use an user variables in MySQL LIKE clause?
MysqlSqlDatabaseMysql Problem Overview
I am trying to setup a few simple SQL scripts to help with some short term DB administration. As such, I'm setting up variables to try to make it easier to reuse these scripts.
The problem I'm having is specifically with the LIKE clause.
SET @email = '[email protected]';
SELECT email from `user` WHERE email LIKE '%@email%';
So I want to have it finding results based on the email SET in the variable. The query works if I manually enter the email into the LIKE clause.
How can I get the LIKE clause to work with the user variable?
UPDATE: @dems's answer works for this simple case, but I'm having trouble with a more complex query.
SET @email = '[email protected]';
SELECT project.projectno, project.projectname, login.username,
CONCAT(login.firstname, ' ', login.lastname), projectuser.title
FROM projectuser
INNER JOIN login ON projectuser.uid = login.uid
LEFT JOIN project ON projectuser.pid = project.pid
WHERE login.username LIKE CONCAT ('%', @email, '%')
Gives me the error "FUNCTION mydb.CONCAT does not exist"
The query works without the CONCAT():
SET @email = '[email protected]';
SELECT project.projectno, project.projectname, login.username,
CONCAT(login.firstname, ' ', login.lastname), projectuser.title
FROM projectuser
INNER JOIN login ON projectuser.uid = login.uid
LEFT JOIN project ON projectuser.pid = project.pid
WHERE login.username LIKE @email
Mysql Solutions
Solution 1 - Mysql
SET @email = 'test@test.com';
SELECT email from `user` WHERE email LIKE CONCAT('%', @email, '%');
Solution 2 - Mysql
You may have error
Error Code: 1267. Illegal mix of collations for operation 'like' 0.016 sec
In this case you need to specify the same collation as used for your table like that:
SET @email = '[email protected]' COLLATE utf8_unicode_ci;
Solution 3 - Mysql
I resolved using the CONCAT function before using LIKE statement:
SET @email = '[email protected]';
set @email = CONCAT('%',@email,'%');
SELECT email from `user` WHERE email LIKE @email;
It works fine for me
Solution 4 - Mysql
No need to CONCAT just use '%'+ Variable +'%':
SET @email = '[email protected]';
SELECT email from `user` WHERE email LIKE '%' + @email + '%';
Solution 5 - Mysql
BEGIN
SET @emailid = CONCAT('%', '[email protected]' ,'%');
SET @t1 =CONCAT('SELECT * FROM user WHERE email LIKE ''', @emailid, '''');
PREPARE stmt3 FROM @t1;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
END
Solution 6 - Mysql
Jan '22 I was having an issue getting the LIKE command to work. I discovered through echoing the variable value and my SQL statement that the quote function puts tick marks around the variable. code to get business type:
isset($_POST['button']) ? $businesstype = get_post($pdo, 'button'):
$businesstype = get_post($pdo, 'businesstype');
function get_post($pdo, $var)
{
return htmlentities($pdo->quote($_POST[$var]));
}
echo of $businesstype: 'Barber'
echo of SQL: Select BusinessName, MemberNum, Street, City FROM member WHERE verified = TRUE AND businesstype LIKE 'Barber' Order by featurelevel desc, membershipdate desc,businessname
Since the single quotes were already around the search value, to use LIKE I had to get the % after the first tick mark, and before the last. I used a combination of a string replace function and concat operators: replacing the original single quotes with nothing, and concatting the '% and %' around the search value.
$businesstype ="'%" . str_ireplace("'","",$businesstype) . "%'";
. Worked for me.I hope it helps someone. Let me know if I am undoing the SQL injection safeguard somehow.
Solution 7 - Mysql
Using same syntax as oracle seems to work:
SELECT email from user WHERE email LIKE '%' || @email || '%';