Using Variable in MySql Regex Query

Question

I am attempting to search multiple columns for tags and returning the type.

Below is a simplified version of my query.

set @item_1 = '(Tag1|Tag2|Tag3|Tag4)(,| |$)',
    @item_2 = '(Tag5|Tag6|Tag7|Tag8)(,| |$)';
select
  case
    when tags_1 regexp @item_1
      then 'Item_1'
    when tags_2 regexp @item_1
      then 'Item_1'
    when tags_1 regexp @item_2
      then 'Item_2'
    when tags_2 regexp @item_2
      then 'Item_2'
    else 'Uncategorised'
  end type
from my_table
order by date desc

Is there any way to set the tags as a variable and the use them within regex. I have tried the above and am getting the error below.

[ERROR in query 2] Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation 'regexp'

Thanks.

what is your table collation ?

developerCK
– developerCK

2016-02-04 11:06:31 +00:00
Commented Feb 4, 2016 at 11:06 — developerCK
– developerCK, Commented Feb 4, 2016 at 11:06

duduwe · Accepted Answer · 2016-02-04 11:17:05Z

1

Add COLLATE every after REGEXP expression.

set @item_1 = 'Tag1|Tag2|Tag3|Tag4',
    @item_2 = 'Tag5|Tag6|Tag7|Tag8';
select
  case
    when tags_1 regexp concat(@item_1, '(%)(,|$)') COLLATE utf8_unicode_ci
      then 'Item_1'
    when tags_2 regexp concat(@item_1, '(%)(,|$)') COLLATE utf8_unicode_ci
      then 'Item_1'
    when tags_1 regexp concat(@item_2, '(%)(,|$)') COLLATE utf8_unicode_ci
      then 'Item_2'
    when tags_2 regexp concat(@item_2, '(%)(,|$)') COLLATE utf8_unicode_ci
      then 'Item_2'
    else 'Uncategorised'
  end type
from my_table
order by date desc;

Another work-around is to change your table collation to latin1 - default collate / Schema Default. You do not anymore have to specify COLLATE.

answered Feb 4, 2016 at 11:17

duduwe

1,32214 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Variable in MySql Regex Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related