Hi,
I want to look in table comment column comment-text for %Unsinn% case-insensitive with this clause:
where comment_text collate utf8mb4_general_ci like '%Unsinn%';
but this gives me error: COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'binary'
How can I proceed such a collation?
Kind regards Doc Taxon ...
Hello Martin, where are you trying to execute that query exactly?
суб, 1. јун 2024. у 17:57 Martin Domdey dr.taxon@gmail.com је написао/ла:
Hi,
I want to look in table comment column comment-text for %Unsinn% case-insensitive with this clause:
where comment_text collate utf8mb4_general_ci like '%Unsinn%';
but this gives me error: COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'binary'
How can I proceed such a collation?
Kind regards Doc Taxon ...
Wikitech-l mailing list -- wikitech-l@lists.wikimedia.org To unsubscribe send an email to wikitech-l-leave@lists.wikimedia.org https://lists.wikimedia.org/postorius/lists/wikitech-l.lists.wikimedia.org/
Hello Zoran,
it's WMF replica database using dewiki_p.
But LOWER() doesn't work too, see below:
SELECT log_title, log_action, log_timestamp, log_comment_id, comment_text, comment_data FROM logging JOIN comment ON comment_id = log_comment_id WHERE !log_namespace AND log_action = 'delete' AND log_timestamp < 20240601000000 AND log_timestamp >= 20240531000000 AND LOWER(comment_text) LIKE '%unsinn%' ORDER BY log_timestamp;
Am Sa., 1. Juni 2024 um 18:38 Uhr schrieb Zoran Dori < zorandori4444@gmail.com>:
Hello Martin, where are you trying to execute that query exactly?
суб, 1. јун 2024. у 17:57 Martin Domdey dr.taxon@gmail.com је написао/ла:
Hi,
I want to look in table comment column comment-text for %Unsinn% case-insensitive with this clause:
where comment_text collate utf8mb4_general_ci like '%Unsinn%';
but this gives me error: COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'binary'
How can I proceed such a collation?
Kind regards Doc Taxon ...
Wikitech-l mailing list -- wikitech-l@lists.wikimedia.org To unsubscribe send an email to wikitech-l-leave@lists.wikimedia.org
https://lists.wikimedia.org/postorius/lists/wikitech-l.lists.wikimedia.org/
Wikitech-l mailing list -- wikitech-l@lists.wikimedia.org To unsubscribe send an email to wikitech-l-leave@lists.wikimedia.org https://lists.wikimedia.org/postorius/lists/wikitech-l.lists.wikimedia.org/
I think you want CONVERT(comment_text USING utf8mb4)
You need to convert to utf8 before you can collate as utf8.
I suspect this query would be pretty slow.
-- Bawolff
On Saturday 1 June 2024, Martin Domdey dr.taxon@gmail.com wrote:
Hi,
I want to look in table comment column comment-text for %Unsinn% case-insensitive with this clause:
where comment_text collate utf8mb4_general_ci like '%Unsinn%';
but this gives me error: COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'binary'
How can I proceed such a collation?
Kind regards Doc Taxon ...
Hi Brian,
thank you, this helps.
Kind regards Doc Taxon ...
Am Sa., 1. Juni 2024 um 20:28 Uhr schrieb Brian Wolff bawolff@gmail.com:
I think you want CONVERT(comment_text USING utf8mb4)
You need to convert to utf8 before you can collate as utf8.
I suspect this query would be pretty slow.
-- Bawolff
On Saturday 1 June 2024, Martin Domdey dr.taxon@gmail.com wrote:
Hi,
I want to look in table comment column comment-text for %Unsinn% case-insensitive with this clause:
where comment_text collate utf8mb4_general_ci like '%Unsinn%';
but this gives me error: COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'binary'
How can I proceed such a collation?
Kind regards Doc Taxon ...
Wikitech-l mailing list -- wikitech-l@lists.wikimedia.org To unsubscribe send an email to wikitech-l-leave@lists.wikimedia.org https://lists.wikimedia.org/postorius/lists/wikitech-l.lists.wikimedia.org/
wikitech-l@lists.wikimedia.org