deleteing from text table

[glennrocksvb]

Those [FONT], [SIZE] and [COLOR] BBCode tags will be replaced with their corresponding HTML tags when displayed in the posts. The CSS solution I posted should still work in resetting those styles to default/inherited style.

But if you insist on doing the SQL solution over CSS solution, you'll likely need to use REGEXP_REPLACE() which is not simple to do.

[abd]

this's as you said.


And here's the result in the post (Sorry it's arabic language


Is this correct?

UPDATE `text` SET `rawtext` = REGEXP_REPLACE(`rawtext`, '\\[font=[a-zA-Z0-9 ]+\\]', '')

UPDATE `text`SET `rawtext` = REGEXP_REPLACE(`rawtext`, '\\[color=#[a-fA-F0-9]{6}]\\]','');

[glennrocksvb]

It looks like the [color] BBCode tags are not evaluated to its <font> HTML equivalent with color attribute. The CSS will not be applied in that case as it only works with HTML tags not BBCode tags.

I notice that the [color] BBCode tags are invalid/incorrect. Was it caused of the SQL Update query you executed?

[abd]

This's why I decided to remove any old tag there, even if manually.

Regards.
.

[glennrocksvb]

I'm not an expert in regular expressions but try doing a SELECT first and see if any records match and then use the pattern in the UPDATE later.

SELECT * FROM text WHERE rawtext REGEXP '\\[font .+\\]';

This is a safer way to test the REGEXP pattern first without doing the UPDATE yet.

[abd]

Example:
[FONT=Simplified Arabic] = 90 matches


Here's the result

[glennrocksvb]

Try this:

SELECT * FROM text WHERE rawtext REGEXP '\\[font\\s|font\=.+\\]';​

This should cover both [FONT COLOR=XXXXXX] and [FONT=XXXX]

[glennrocksvb]

Btw, I'm not responsible if your data gets messed up with the solutions I'm posting. I recommend doing this in a copy of the database rather than the live database.

[glennrocksvb]

I updated the REGEXP pattern used. See it in the query below.

I also updated the SELECT query to show the preview of the updated rawtext so you can see if the replacements to be done look good.

SELECT rawtext as `rawtext before`, REGEXP_REPLACE(rawtext, '\\[font\\s.+\\]|\\[font\=.+\\]', '') as `rawtext after` FROM text WHERE rawtext REGEXP '\\[font\\s.+\\]|\\[font\=.+\\]';

[glennrocksvb]

I updated the REGEXP pattern used. See it in the query below.

I also updated the SELECT query to show the preview of the updated rawtext so you can see if the replacements to be done look good.

SELECT rawtext as `rawtext before`, REGEXP_REPLACE(rawtext, '\\[font\\s.+\\]|\\[font\=.+\\]', '') as `rawtext after` FROM text WHERE rawtext REGEXP '\\[font\\s.+\\]|\\[font\=.+\\]';

See sample results from my test database:

[glennrocksvb]

Oopps! There's a bug in the query. It also deletes the text between the [FONT] tags.

[glennrocksvb]

This is complicated to fix. It would require a lot of trials and errors. There are a lot of BBCode variations to cover. You need to find the different variations used in the database so you can limit/simplify the REGEXP patterns to use in the query and not try to cover all possible variations.

[abd]

Try this:

SELECT * FROM text WHERE rawtext REGEXP '\\[font\\s|font\=.+\\]';​

This should cover both [FONT COLOR=XXXXXX] and [FONT=XXXX]

Here's the result

[abd]

See sample results from my test database:

This one says:


First total is 15544
Second is 4041

[glennrocksvb]

The SELECT query I provided has a bug in it as I mentioned in the comments in post #25