CODEWITHSUNDEEP
mysqltriggers
Hasan Alsawadi
Hasan Alsawadi

Reputation: 404

MySQL Using a Variable LIKE '%Variable_Name%' in a trigger

I have this little simple trigger..

BEGIN

    DECLARE FILE_NAME VARCHAR(250);
    DECLARE FILE_REFR VARCHAR(500);


SET FILE_NAME = 'Foo';
SET FILE_REFR = 'Bar';

--- I'd like to execute the next statement, using variable FILE_REFR between %% in a LIKE clause:
SELECT COUNT(*) INTO @num_rows FROM referers WHERE filename = FILE_NAME AND ref NOT LIKE "%FILE_REFR%";

...
...
...

END

Unfortunately, the variable name is not being picked up as a variable.. but as a CHAR, I know there is something missing there.

Help is more than appreciated.. :)

Upvotes: 1

Views: 4779

Answers (2)

Aman Aggarwal
Aman Aggarwal

Reputation: 18449

Use the variable as:

CONCAT('%', FILE_REFR, '%');

So the complete select query is:

SELECT COUNT(*) INTO @num_rows FROM referers WHERE filename = FILE_NAME AND ref NOT LIKE  CONCAT('%', FILE_REFR, '%');

Thanks

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Is this what you want?

SELECT COUNT(*) INTO @num_rows
FROM referers
WHERE filename = FILE_NAME AND
      ref NOT LIKE CONCAT('%', FILE_REFR, '%');

MySQL does not find variables inside string literals. Instead you have to use CONCAT() to piece the pattern together.

Upvotes: 1

Related Questions