String variable

i was able to do this with no problem:

SET @myid = 1946;

SELECT * FROM mytable WHERE id = @myid OR sub_id = @myid;

but i cannot seem to make the same if my variable is string for this query:

SELECT * FROM mytable WHERE username LIKE '%my_variable_here%';

anyone please?

SET @string = 'string';
SET @a = 'a';
SET @r = 'r';
SELECT @string LIKE CONCAT('%', @a, '%'), @string LIKE CONCAT('%', @r, '%');

hi, i cant seem to make it work with this:

SET @username = 'john'; SELECT * FROM mytable WHERE username LIKE CONCAT('%', @username , '%');

this query work though: SELECT * FROM mytable WHERE username LIKE CONCAT('%', 'john' , '%');

is there anything im missing?

@ansgar, hi sir, i still need this, can you help me on this?

Hm, not sure. What's the error message here?

I just realized that I am asking on a wrong forum?

This is not about heidisql question but an sql structure problem?

Well, the category is "General", and quite a few people ask SQL query questions here. So you're not too wrong here.

ah, thank you for that.

I'll try to make my question clearer.

What I need is to make a string variable that I can later use on my sql queries.

I can do this with no problem:

SET @myvariable= 1946;

SELECT * FROM mytable WHERE id = @myvariable;

However, I cannot do this:

SET @myvariable= 'john';

SELECT * FROM mytable WHERE username LIKE '%@myvariable%';

Yes, that's what you said above. I just wanted to know the error message you get with it.

Here are what i tried so far :

The first one seems like he treated @myvariable as the actual string and not as a variable.

SELECT column_name FROM mytable WHERE username LIKE '%@myvariable%';

/ Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 2 queries: 0.015 sec. /

Here's an error to the second one that I tried based on Misha's suggestion above.

SELECT column_name FROM admins WHERE name LIKE CONCAT('%', @myvariable, '%');

/ SQL Error (1267): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation 'like' /

/ Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 1 of 2 queries: 0.000 sec. /

I tried to reproduce that CONCAT version, but every attempt works here.

You can probably equalize the collation using the CONVERT() function:

SET @myvar='üöä';
SELECT * FROM admin_user a WHERE a.lastname LIKE CONVERT( CONCAT('%', @myvar, '%') USING utf8);

thank you @ansgar, it works as expected! more power to you! smile here.

sorry for rebumping this thread, but is it possible to include more than 1 variable in the SET @myid = 1946;

lets say where @myid in ('1946','1999', etc)