SQL REPLACE STATEMENT - CONSOLIDATION

[Craig Keightley]

Can these lines of sql statements be consolidated into one sql statement
(possibly using reg exps??)

BEGIN CODE
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++
Update applications Set news_article = replace(news_article, '<FONT
face="Times New Roman" color=#000000 size=3>', '');
Update applications Set news_article = replace(news_article, '<P
class=MsoNormal style="MARGIN: 0cm 0cm 0pt">', '');
Update applications Set news_article = replace(news_article,
'<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office"
/>', '');
Update applications Set news_article = replace(news_article, '<o:p>', '');
Update applications Set news_article = replace(news_article, '</o:p>', '');
Update applications Set news_article = replace(news_article, '</P>', '');
Update applications Set news_intro = replace(news_intro, '<FONT face="Times
New Roman" color=#000000 size=3>', '');
Update applications Set news_intro = replace(news_intro, '<P class=MsoNormal
style="MARGIN: 0cm 0cm 0pt">', '');
Update applications Set news_intro = replace(news_intro, '<?xml:namespace
prefix = o ns = "urn:schemas-microsoft-com:office:office" />', '');
Update applications Set news_intro = replace(news_intro, '<o:p>', '');
Update applications Set news_intro = replace(news_intro, '</o:p>', '');
Update applications Set news_intro = replace(news_intro, '</P>', '');
Update applications Set news_article = replace(news_article, '<SPAN lang=FR
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-ansi-language:
FR">', '');
Update applications Set news_article = replace(news_article, '<SPAN lang=FR
style="COLOR: black; mso-ansi-language: FR">', '');
Update applications Set news_article = replace(news_article, '</SPAN>', '');
Update applications Set news_article = replace(news_article, '<FONT
color=#000000>', '');
Update applications Set news_article = replace(news_article, '</FONT>', '');
Update applications Set news_article = replace(news_article, '<SPAN lang=FR
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-ansi-language:
FR">', '');
Update applications Set news_article = replace(news_article, '<SPAN lang=FR
style="COLOR: black; mso-ansi-language: FR">', '');
Update applications Set news_article = replace(news_article, '</SPAN>', '');
Update applications Set news_article = replace(news_article, '<FONT
color=#000000>', '');
Update applications Set news_article = replace(news_article, '</FONT>', '');
Update applications Set news_intro = replace(news_intro, '<SPAN lang=FR
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-ansi-language:
FR">', '');
Update applications Set news_intro = replace(news_intro, '<SPAN lang=FR
style="COLOR: black; mso-ansi-language: FR">', '');
Update applications Set news_intro = replace(news_intro, '</SPAN>', '');
Update applications Set news_intro = replace(news_intro, '<FONT
color=#000000>', '');
Update applications Set news_intro = replace(news_intro, '</FONT>', '');

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++
END CODE

Many Thanks

[Kevin Thorpe]

Craig Keightley wrote:

Can these lines of sql statements be consolidated into one sql statement
(possibly using reg exps??)

BEGIN CODE
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++
Update applications Set news_article = replace(news_article, '<FONT
face="Times New Roman" color=#000000 size=3>', '');
Update applications Set news_article = replace(news_article, '<P
.... etc. etc.

What are you trying to do? These statments are to clean HTML tags from
records in your database. If it's a one-off to clean up your database
then just run it as is. If it's a regular job then you ought to look
into cleaning the data before it gets there.

strip_tags is very useful for cleanup purposes. You can specify which
tags to explicitly allow (<b>,<i> etc) and write the clean version to
your database.

[Gordon Burditt]

>Can these lines of sql statements be consolidated into one sql statement

(possibly using reg exps??)
You might be able to put this in one big mess:

Update applications Set news_article = replace(replace(replace(replace(replace(replace(re place(replace(replace(news_article, '<SPAM>', ''), '</SPAM>', '') ...

but it looks very messy to maintain. You can also do news_article
and news_intro in the same statement.

Gordon L. Burditt
BEGIN CODE
+++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++
Update applications Set news_article = replace(news_article, '<FONT
face="Times New Roman" color=#000000 size=3>', '');
Update applications Set news_article = replace(news_article, '<P
class=MsoNormal style="MARGIN: 0cm 0cm 0pt">', '');
Update applications Set news_article = replace(news_article,
'<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office"
/>', '');
Update applications Set news_article = replace(news_article, '<o:p>', '');

....

[Craig Keightley]

Hopefully its a one off
I have a page where the user can isert a record but it uses a div id
instead of a textarea When the form is submitted, it passes the data in the
div id to a hidden field and the hidden field is entered into the db.
The reason for this is the user can format the text to make it bold text and
or italic. But thats all they can do. If a user copies and pastes text
froma word document, it also copies the format of the text from that
document. Therefore all the additional xmal items are included.
I would use strip_tags but the allowable tags are used in the document also
(<P> tag etc)

Thanks for the help though

Craig
"Kevin Thorpe" <ke***@pricetrak.com> wrote in message
news:41***********************@news.easynet.co.uk. ..

Craig Keightley wrote:

Can these lines of sql statements be consolidated into one sql statement
(possibly using reg exps??)

BEGIN CODE
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++
Update applications Set news_article = replace(news_article, '<FONT
face="Times New Roman" color=#000000 size=3>', '');
Update applications Set news_article = replace(news_article, '<P ....
etc. etc.

What are you trying to do? These statments are to clean HTML tags from
records in your database. If it's a one-off to clean up your database then
just run it as is. If it's a regular job then you ought to look into
cleaning the data before it gets there.

strip_tags is very useful for cleanup purposes. You can specify which tags
to explicitly allow (<b>,<i> etc) and write the clean version to your
database.

[Alvaro G. Vicario]

*** Craig Keightley escribió/wrote (Wed, 6 Oct 2004 12:15:47 +0100):

Can these lines of sql statements be consolidated into one sql statement
(possibly using reg exps??)

You're going to kill your database server, which is the typical bottleneck
in a dynamic web site. My suggestion:

1) Write a PHP function to cleanup text
2) Get data from DB
3) Clean data with your PHP function
4) Put data back into DB
--
-+ Álvaro G. Vicario - Burgos, Spain
+- http://www.demogracia.com (la web de humor barnizada para la intemperie)
++ Las dudas informáticas recibidas por correo irán directas a la papelera
-+ I'm not a free help desk, please don't e-mail me your questions
--