Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

String variable

kapitannwel's profile image kapitannwel posted 1 year ago in General Permalink

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?

Misha v.3's profile image Misha v.3 posted 1 year ago Permalink
SET @string = 'string';
SET @a = 'a';
SET @r = 'r';
SELECT @string LIKE CONCAT('%', @a, '%'), @string LIKE CONCAT('%', @r, '%');
kapitannwel's profile image kapitannwel posted 1 year ago Permalink

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?

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
kapitannwel's profile image kapitannwel posted 10 months ago Permalink

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

ansgar's profile image ansgar posted 10 months ago Permalink

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

kapitannwel's profile image kapitannwel posted 10 months ago Permalink

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

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

ansgar's profile image ansgar posted 10 months ago Permalink

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

kapitannwel's profile image kapitannwel posted 10 months ago Permalink

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%';

ansgar's profile image ansgar posted 10 months ago Permalink

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

kapitannwel's profile image kapitannwel posted 10 months ago Permalink

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. /

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 10 months ago Permalink

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);
kapitannwel's profile image kapitannwel posted 10 months ago Permalink

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

Please login to leave a reply, or register at first.