I have a query like below to remove from a table column certain substrings which begin and end with particular substrings:
UPDATE om_posts SET post_content=REPLACE(post_content, SUBSTRING(
post_content,
LOCATE(' style="', post_content),
LOCATE('"', post_content, LOCATE(' style="', post_content )+ 8) - LOCATE(' style="', post_content ) + 1
),'')
where post_type="post";
I want to make this better reusable, so I'd like to abstract out those strings. I came across user-defined variables in mysql and refactored like this:
SET @beginning = ' style="';
SET @ending ='"';
UPDATE om_posts SET post_content=REPLACE(post_content, SUBSTRING(
post_content,
LOCATE(@beginning, post_content),
LOCATE(@ending, post_content, LOCATE(@beginning, post_content )+ 8) - LOCATE(@beginning, post_content ) + 1
),'')
where post_type="post";
but this gives an error: Error in query (1267): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation 'locate'
. As far as I can tell my syntax should be correct. What am I missing?
Every character string literal has a character set and a collation.
For the simple statement SELECT 'string', the string has the connection default character set and collation defined by the character_set_connection and collation_connection system variables.
A character string literal may have an optional character set introducer and COLLATE clause, to designate it as a string that uses a particular character set and collation:
[_charset_name]'string' [COLLATE collation_name]
from the official documentation
example from that page: _utf8mb4'abc' COLLATE utf8mb4_danish_ci