Maybe someone here can help me with messed up Tags

Hi

Hi

I had Zoints Tags running for a long time and because vBulletins Tag System does not offer an automated tag non tagged thread feature I converted my old tags to the vBulletin tags using this converter: Convert Zoints Thread Tags to vB 3.7 tagging system - vBulletin.org Forum

However, Zoints Tags is a little buggy and there are tags that are defect (like a "0" tag or any single digit number for that matter) which had no threads associated to it but shows up in the tag cloud.
Also it allows non url conform characters like : and so on.

There is currently no way for me to remove these tags (I can't delete them by hand via ACP, I have over 400 pages of Tags).

Can anyone here help me out with the necessary SQL queries to remove all tags that contain non url confirm characters like : or . or anything like that and single character tags also?

That would be really much appreciated

Thank you in advance

StarBuG

Hello,

I suppose this issue is not related to vBSEO? What is an example tag/URL with the problem?

I know that it is not related to vBSEO that is why I wrote "maybe"

an example with : in the url:

ausland: Beiträge

and an example of a defect tag with single character:

Die häufigsten Tags

i'm not sure where the tags go after the conversion (i'm still on zoints, and my vb tables are empty)

it's complicated, because they have 3 tables... tag, tagthread, and tagsearch.

basically you need to find all the tag id's...

select tagid from tag where ((tagtext like '%0%') or (tagtext like '%OTHER_WEIRD_CHAR_HERE) or (length(tagtext) < 2))

with that list, you then delete.

you CAN do it all at once if you're on a modern mysql version, once per table... something like:



delete from tagthread where tagid IN (select tagid from tag WHERE ((tagtext like '%0%') or (tagtext like '%OTHER_WEIRD_CHAR_HERE) or (length(tagtext) < 2)))

delete from tagsearch where tagid IN (select tagid from tag WHERE ((tagtext like '%0%') or (tagtext like '%OTHER_WEIRD_CHAR_HERE) or (length(tagtext) < 2)))

delete from tag where ((tagtext like '%0%') or (tagtext like '%OTHER_WEIRD_CHAR_HERE) or (length(tagtext) < 2))




DO A BACKUP FIRST!
UNTESTED, UNDOCUMENTED

/lawyer

You can try this query to remove all "digit tags":
DELETE FROM tag WHERE tagtext REGEXP '^[0-9]+$'

Originally Posted by Oleg Ignatiuk

You can try this query to remove all "digit tags":
DELETE FROM tag WHERE tagtext REGEXP '^[0-9]+$'

you'd also want to remove them from the thread and search as well, to avoid orphaned records.... so 3 queries again...


delete from tagthread where tagid in (select tagid from tag WHERE tagtext REGEXP '^[0-9]+$')

delete from tagsearch where tagid in (select tagid from tag WHERE tagtext REGEXP '^[0-9]+$')

and then finally the orig. query:
delete from tag where WHERE tagtext REGEXP '^[0-9]+$'

ok

after these queries still not all wrong tags are deleted.

The regexp does not seem to match all tags I want to delete

I want to delete:

1) all single character tags (like "0", or "1", or "a")
2) all tags that contain non url confirm characters like : or . (eg: "article:")
3) if possible also all 2 character tags like "ab" or "01" or so

so the only characters allowed should be: a-z, 0-9
and the only tags left should be tags that only consist of numbers and letters

Thank you very much for you help here.

StarBuG

does this pull all your GOOD tags? ie, you don't see anything bad?

Code:

 select * from tag WHERE tagtext REGEXP '^[0-9a-zA-Z\s_-]{2,99}$'

and does this pull ALL your bad tags?

Code:

select * from tag WHERE tagtext REGEXP '^![0-9a-zA-Z\s_-]{2,99}$'

this regular expression will look for tags that:
1) 2-99 chars in length (field is set to hold 100 chars, but i doubt any are that long)
2) contains only numbers, letters, spaces, underscores and dashes



if that looks like a good dataset, you can run these 3 queries:

Code:

delete from tagthread where tagid IN ( select tagid from tag WHERE tagtext REGEXP '^![0-9a-zA-Z\s_-]{2,99}$')

delete from tagsearch where tagid IN ( select tagid from tag WHERE tagtext REGEXP '^![0-9a-zA-Z\s_-]{2,99}$')

delete from tag WHERE tagtext REGEXP '^![0-9a-zA-Z\s_-]{2,99}$'

you may wish to remove _ and - if you don't want those in there as well... but \s is required (space) for multi-word tags

I get: Deleted rows: 0 (Query took 0.7475 sec) when I execute

delete from tagthread where tagid IN ( select tagid from tag WHERE tagtext REGEXP '^![0-9a-zA-Z\s_-]{2,99}$')

seems to be something wrong with the query?

No more Ideas?