By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,919 Members | 1,096 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,919 IT Pros & Developers. It's quick & easy.

SQL REPLACE STATEMENT - CONSOLIDATION

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.

Jul 17 '05 #2

P: n/a
>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>', '');

....
Jul 17 '05 #3

P: n/a
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.

Jul 17 '05 #4

P: n/a
*** 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
--
Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.