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_ar ticle, '<FONT
face="Times New Roman" color=#000000 size=3>', '');
Update applications Set news_article = replace(news_ar ticle, '<P
class=MsoNormal style="MARGIN: 0cm 0cm 0pt">', '');
Update applications Set news_article = replace(news_ar ticle,
'<?xml:namespac e prefix = o ns = "urn:schema s-microsoft-com:office:offi ce"
/>', '');
Update applications Set news_article = replace(news_ar ticle, '<o:p>', '');
Update applications Set news_article = replace(news_ar ticle, '</o:p>', '');
Update applications Set news_article = replace(news_ar ticle, '</P>', '');
Update applications Set news_intro = replace(news_in tro, '<FONT face="Times
New Roman" color=#000000 size=3>', '');
Update applications Set news_intro = replace(news_in tro, '<P class=MsoNormal
style="MARGIN: 0cm 0cm 0pt">', '');
Update applications Set news_intro = replace(news_in tro, '<?xml:namespac e
prefix = o ns = "urn:schema s-microsoft-com:office:offi ce" />', '');
Update applications Set news_intro = replace(news_in tro, '<o:p>', '');
Update applications Set news_intro = replace(news_in tro, '</o:p>', '');
Update applications Set news_intro = replace(news_in tro, '</P>', '');
Update applications Set news_article = replace(news_ar ticle, '<SPAN lang=FR
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-ansi-language:
FR">', '');
Update applications Set news_article = replace(news_ar ticle, '<SPAN lang=FR
style="COLOR: black; mso-ansi-language: FR">', '');
Update applications Set news_article = replace(news_ar ticle, '</SPAN>', '');
Update applications Set news_article = replace(news_ar ticle, '<FONT
color=#000000>' , '');
Update applications Set news_article = replace(news_ar ticle, '</FONT>', '');
Update applications Set news_article = replace(news_ar ticle, '<SPAN lang=FR
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-ansi-language:
FR">', '');
Update applications Set news_article = replace(news_ar ticle, '<SPAN lang=FR
style="COLOR: black; mso-ansi-language: FR">', '');
Update applications Set news_article = replace(news_ar ticle, '</SPAN>', '');
Update applications Set news_article = replace(news_ar ticle, '<FONT
color=#000000>' , '');
Update applications Set news_article = replace(news_ar ticle, '</FONT>', '');
Update applications Set news_intro = replace(news_in tro, '<SPAN lang=FR
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-ansi-language:
FR">', '');
Update applications Set news_intro = replace(news_in tro, '<SPAN lang=FR
style="COLOR: black; mso-ansi-language: FR">', '');
Update applications Set news_intro = replace(news_in tro, '</SPAN>', '');
Update applications Set news_intro = replace(news_in tro, '<FONT
color=#000000>' , '');
Update applications Set news_intro = replace(news_in tro, '</FONT>', '');

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

Many Thanks

[Kevin Thorpe]

Re: SQL REPLACE STATEMENT - CONSOLIDATION

Craig Keightley wrote:
[color=blue]
> 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_ar ticle, '<FONT
> face="Times New Roman" color=#000000 size=3>', '');
> Update applications Set news_article = replace(news_ar ticle, '<P
> .... etc. etc.[/color]

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]

Re: SQL REPLACE STATEMENT - CONSOLIDATION

>Can these lines of sql statements be consolidated into one sql statement[color=blue]
>(possibly using reg exps??)[/color]

You might be able to put this in one big mess:

Update applications Set news_article = replace(replace (replace(replac e(replace(repla ce(replace(repl ace(replace(new s_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[color=blue]
>
>BEGIN CODE
>++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++
>Update applications Set news_article = replace(news_ar ticle, '<FONT
>face="Times New Roman" color=#000000 size=3>', '');
>Update applications Set news_article = replace(news_ar ticle, '<P
>class=MsoNorma l style="MARGIN: 0cm 0cm 0pt">', '');
>Update applications Set news_article = replace(news_ar ticle,
>'<?xml:namespa ce prefix = o ns = "urn:schema s-microsoft-com:office:offi ce"
>/>', '');
>Update applications Set news_article = replace(news_ar ticle, '<o:p>', '');[/color]
....

[Craig Keightley]

Re: SQL REPLACE STATEMENT - CONSOLIDATION

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" <kevin@pricetra k.com> wrote in message
news:4163d967$0 $14429$afc38c87 @news.easynet.c o.uk...[color=blue]
> Craig Keightley wrote:
>[color=green]
>> 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_ar ticle, '<FONT
>> face="Times New Roman" color=#000000 size=3>', '');
>> Update applications Set news_article = replace(news_ar ticle, '<P ....
>> etc. etc.[/color]
>
> 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.
>[/color]

[Alvaro G. Vicario]

Re: SQL REPLACE STATEMENT - CONSOLIDATION

*** Craig Keightley escribió/wrote (Wed, 6 Oct 2004 12:15:47 +0100):[color=blue]
> Can these lines of sql statements be consolidated into one sql statement
> (possibly using reg exps??)[/color]

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
--