Replacing all entries with "Re:"

[ss.wisch]

Ok here's the thing... I have a vBulletin forum database... there is a
table in there called "post" which has all of my forums posts in it.

What I have are many posts with regular titles like "Oh hello how are
you" or whatever, and many posts (which are replies) with "Re:" before
them and whatever message following that.

I have recently updated my forums not to show the Re: thing, and I need
a quick way to execute a query or something that will CLEAR all titles
that have this "Re:" in the title area of the post table entries.

Anyone know how I can do this?

The table is called "post" and the cell (?) I guess is called "title."

There are over 8,000 entries in this post table so I really need to
find an efficient way to remove all of the titles with "Re:" ...
manually is just too long and too hard.

Thanks for your help.... by the way I'm running MySQL version 4.0.25 I
believe.

[Thomas Bartkus]

<ss******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Ok here's the thing... I have a vBulletin forum database... there is a
table in there called "post" which has all of my forums posts in it.

What I have are many posts with regular titles like "Oh hello how are
you" or whatever, and many posts (which are replies) with "Re:" before
them and whatever message following that.

I have recently updated my forums not to show the Re: thing, and I need
a quick way to execute a query or something that will CLEAR all titles
that have this "Re:" in the title area of the post table entries.

Anyone know how I can do this?

The table is called "post" and the cell (?) I guess is called "title."

There are over 8,000 entries in this post table so I really need to
find an efficient way to remove all of the titles with "Re:" ...
manually is just too long and too hard.

Thanks for your help.... by the way I'm running MySQL version 4.0.25 I
believe.

Well - what is it you want to do? It helps to be specific!
It is unclear whether you are trying to query or you wish to actually modify
the data in your table.

------------------------------------
Query for only titles that don't begin "Re:"

SELECT title
FROM post
WHERE NOT (title LIKE 'Re:%')
------------------------------------------
Query all titles but hide the leading "Re:"

SELECT title
IF (LEFT(title,3)='Re', RIGHT(title, LENGTH(title)-3), title) As
AdjustedTitle
FROM post
--------------------------------------------
Surely you didn't mean to delete those records when you said "CLEAR"

DELETE FROM post
WHERE title LIKE 'Re:';

------------------------------------------------
Or perhaps you meant to keep the records but remove the leading "Re:" from
[titles] in the table post !

UPDATE post
SET title = LEFT(title,3)='Re', RIGHT(title, LENGTH(title)-3)
WHERE title LIKE 'Re:';
---------------------------------------------------------

Thomas Bartkus

[Thomas Bartkus]

<ss******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Ok here's the thing... I have a vBulletin forum database... there is a
table in there called "post" which has all of my forums posts in it.

What I have are many posts with regular titles like "Oh hello how are
you" or whatever, and many posts (which are replies) with "Re:" before
them and whatever message following that.

I have recently updated my forums not to show the Re: thing, and I need
a quick way to execute a query or something that will CLEAR all titles
that have this "Re:" in the title area of the post table entries.

Anyone know how I can do this?

The table is called "post" and the cell (?) I guess is called "title."

There are over 8,000 entries in this post table so I really need to
find an efficient way to remove all of the titles with "Re:" ...
manually is just too long and too hard.

Thanks for your help.... by the way I'm running MySQL version 4.0.25 I
believe.

Well - what is it you want to do? It helps to be specific!
It is unclear whether you are trying to query or you wish to actually modify
the data in your table.

------------------------------------
Query for only titles that don't begin "Re:"

SELECT title
FROM post
WHERE NOT (title LIKE 'Re:%')
------------------------------------------
Query all titles but hide the leading "Re:"

SELECT title
IF (LEFT(title,3)='Re', RIGHT(title, LENGTH(title)-3), title) As
AdjustedTitle
FROM post
--------------------------------------------
Surely you didn't mean to delete those records when you said "CLEAR"

DELETE FROM post
WHERE title LIKE 'Re:%';

------------------------------------------------
Or perhaps you meant to keep the records but remove the leading "Re:" from
[titles] in the table post !

UPDATE post
SET title = LEFT(title,3)='Re', RIGHT(title, LENGTH(title)-3)
WHERE title LIKE 'Re:%';
---------------------------------------------------------

Thomas Bartkus

[ss.wisch]

Hey Thomas!
Thanks for helping me out

heres what I need to do

I have a table, called post, and theres a section called "title" ....
tons and tons of titles on all my entries, but I want to clear out all
the titles that contain "Re:" in them so that only ones without "Re:"
remain.

Your help is greatly appreciated!

[Thomas Bartkus]

<ss******@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hey Thomas!
Thanks for helping me out

heres what I need to do

I have a table, called post, and theres a section called "title" ....
tons and tons of titles on all my entries, but I want to clear out all
the titles that contain "Re:" in them so that only ones without "Re:"
remain.

Your help is greatly appreciated!

Tables don't have "section"s. I believe you meant "field" or "column" and
your goal is to delete all records (rows) where the entry in the title
(column=field) begins with "Re:".

If this is so, you already have it:

DELETE FROM post
WHERE title LIKE 'Re:%';

Bang! They're gone.
No more records (rows!) where the title begins with "Re:".

AND no way to "undo" unless you have a backup!
Thomas Bartkus

[ss.wisch]

Thomas..... you are my hero. If there is any way I can repay you let me
know. If you have a site or service, I will def. help advertise.

Theo

[ss.wisch]

http://img483.imageshack.us/img483/8664/okok4da.jpg

Please see that screenshot

My goal is to erase the titles that have "Re:" in them.

Thanks again for your help man!!

[ss.wisch]

But remember I still want to keep the actual entries in post... all of
them.

[ss.wisch]

I was interested in your previous replies and saw this suggestion you
made, Thomas:
Or perhaps you meant to keep the records but remove the leading "Re:"
from
[titles] in the table post !

UPDATE post
SET title = LEFT(title,3)='Re', RIGHT(title, LENGTH(title)-3)
WHERE title LIKE 'Re:%';

Perhaps a modification of that would allow keeping the records but
moving the entire title, which is exactly what I want to do with the
titles containing Re:

Thanks so much again
Theo

[ss.wisch]

I was interested in your previous replies and saw this suggestion you
made, Thomas:
Or perhaps you meant to keep the records but remove the leading "Re:"
from
[titles] in the table post !

UPDATE post
SET title = LEFT(title,3)='Re', RIGHT(title, LENGTH(title)-3)
WHERE title LIKE 'Re:%';

Perhaps a modification of that would allow keeping the records but
removing the entire title, which is exactly what I want to do with the
titles containing Re:

Thanks so much again

Theo

[Thomas Bartkus]

<ss******@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

But remember I still want to keep the actual entries in post... all of
them.

Dang! Here we go again.

This sounds like you want to *keep* all rows (records!) but simply remove
the "Re:" that appears in front of some of the [title] strings - leaving the
remainder of the [title] string intact.

If *this* is the case then:

UDPATE post
SET title = TRIM(MID(title, 4, 255))
WHERE title LIKE 'Re:%'

Again - if you screw up without a backup - you're screwed! That command is
irrevocable.

Damn computers will do exactly what you tell them but seldom what you want
:-)
You do have a backup - don't you?
Thomas Bartkus

[Thomas Bartkus]

<ss******@gmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

Thomas..... you are my hero. If there is any way I can repay you let me
know. If you have a site or service, I will def. help advertise.

Theo

Send beer!
Thomas Bartkus

[ss.wisch]

Hey Thomas...

See this link real quick if you could!
http://www.vbulletin.com/forum/showthread.php?t=161023

Marco says

UPDATE post set title = ''
WHERE title LIKE 'Re: %'
AND parentid <> 0;
And I want to keep ALL entries/record in the POST table, just want to
simply erase all titles (REMOVE ALL TITLES) from those entries that
have "Re:" in the title.... all of the re:s I want to remove are in the
very beginning too by the way.

If you could suggest an alternative or verify that above post on
vbulletin.com that would be AWESOME!

thanks !!!

[Thomas Bartkus]

"Thomas Bartkus" <th***********@comcast.net> wrote in message
news:Vp********************@telcove.net...

<ss******@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

But remember I still want to keep the actual entries in post... all of
them.
Dang! Here we go again.

This sounds like you want to *keep* all rows (records!) but simply remove
the "Re:" that appears in front of some of the [title] strings - leaving

the remainder of the [title] string intact.

If *this* is the case then:

UDPATE post
SET title = TRIM(MID(title, 4, 255))
WHERE title LIKE 'Re:%'

Again - if you screw up without a backup - you're screwed! That command is irrevocable.

Damn computers will do exactly what you tell them but seldom what you want
:-)
You do have a backup - don't you?
Thomas Bartkus

Or if you want to keep each row but clear the entire [title] if it begins
"Re:"

UPDATE post
SET title = ""
WHERE title LIKE 'Re:%'

Irrevocable! Same caveats apply.

You could also set title = Null providing your table definition permits
Nulls here.
Thomas Bartkus

[ss.wisch]

awesome... figured it out..thanks for all the help!