Ads were blocked by your webbrowser.

This is not a 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 10 months ago in General

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 10 months ago
SET @string = 'string';
SET @a = 'a';
SET @r = 'r';
SELECT @string LIKE CONCAT('%', @a, '%'), @string LIKE CONCAT('%', @r, '%');
kapitannwel's profile image kapitannwel posted 10 months ago

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 by your webbrowser.

This is not a 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 2 months ago

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

ansgar's profile image ansgar posted 2 months ago

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

kapitannwel's profile image kapitannwel posted 2 months ago

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 2 months ago

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 2 months ago

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 2 months ago

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 2 months ago

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 by your webbrowser.

This is not a 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 2 months ago

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 2 months ago

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

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