FREE AI-Enhanced Online MySQL Compiler - For learning & practice

Untitled*

You must execute the code again to save those values in the database.

-- Create the messages table
CREATE TABLE smf_messages (
    id_msg INT AUTO_INCREMENT PRIMARY KEY,
    id_topic INT NOT NULL,
    id_board INT NOT NULL,
    poster_name VARCHAR(50),
    body TEXT
);

-- Insert sample data
INSERT INTO smf_messages (id_topic, id_board, poster_name, body) VALUES
(1, 1, 'User1', 'This is a normal post without any mention.'),
(1, 1, 'User2', 'Hey @mods, I need some help here!'), -- Mention without quotes
(2, 1, 'User3', '[quote]This is a quoted message mentioning @mods.[/quote] But I also need help, @mods.'), -- Both quoted and unquoted mention
(2, 1, 'User4', 'Just a simple post with no mention or quotes.'),
(3, 1, 'User5', '[quote]Quoted text without any mention.[/quote] Here is an unquoted mention: @mods.'), -- Mention outside quote
(3, 1, 'User6', '[quote]Quoted text with @mods mention only.[/quote]'), -- Mention only in quote
(4, 1, 'User7', 'This post contains @mods but no quotes at all.'), -- Mention without quotes
(4, 1, 'User8', 'Random text without any special mentions or quotes.'),
(5, 1, 'User9', '[quote]Another quoted message.[/quote] Nothing else here.'),
(5, 1, 'User10', 'Final post mentioning @mods for support.'); -- Mention without quotes

SELECT id_msg, id_topic, id_board, poster_name, body
FROM smf_messages
WHERE body LIKE '%@mods%'
  AND REGEXP_REPLACE(body, '\\[quote.*?\\].*?\\[/quote\\]', '') LIKE '%@mods%';

id_msg	id_topic	id_board	poster_name	body
2	1	1	User2	Hey @mods, I need some help here!
3	2	1	User3	[quote]This is a quoted message mentioning @mods.[/quote] But I also need help, @mods.
5	3	1	User5	[quote]Quoted text without any mention.[/quote] Here is an unquoted mention: @mods.
7	4	1	User7	This post contains @mods but no quotes at all.
10	5	1	User10	Final post mentioning @mods for support.