MYSQL 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

[Joe Smith]

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

Can't you pre-process your field -to remove the Html tags- before inserting
it to the DB?. I'm sure you'll find a library to handle the Html to Text
conversion.

The method you're using is probably slower, and surely, more likely to
produce errors. What if you have:
"<FONT color=#000000>" in your html (double space between FONT and color)?
The replacement won't be done.

[Bill Karwin]

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_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">', '');

....

MySQL supports a regular expression matching predicate, but is not a
substitution function. See http://dev.mysql.com/doc/mysql/en/Regexp.html.

I would solve this not in SQL, but in code. That is, write an
application that reads the values from your `applications` table,
performs the substitutions based on regular expressions, and then if any
substitutions were made, updates that record.

run query SELECT PrimaryKeyField , news_article from applications
loop over result set
{
loop over your list of patterns
{
s/pattern//, check for success
}
if any substitutions succeeded then
{
update application set news_article = ?
where PrimaryKeyField = ?
}
}

This isn't a one-line solution either, but you have more flexibility
with regular expressions, as you pointed out. And putting it into a
script might allow you to maintain the list of patterns more easily.

Don't break your back trying to turn every database task into a single
SQL statement. Sometimes it's better just to get it done!

Regards,
Bill K.

[Hilarion]

Combined it into one big ugly query by nesting replacements (removed some repeated replacements):
Update applications Set
news_article =
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
news_article,
'<?xml:namespac e prefix = o ns = "urn:schema s-microsoft-com:office:offi ce" />', '' ),
'<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt">', '' ),
'</P>', ''),
'<o:p>', ''),
'</o:p>', ''),
'<FONT face="Times New Roman" color=#000000 size=3>', '' ),
'<FONT color=#000000>' , ''),
'</FONT>', ''),
'<SPAN lang=FR style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-ansi-language: FR">', ''),
'<SPAN lang=FR style="COLOR: black; mso-ansi-language: FR">', ''),
'</SPAN>', ''),
news_intro =
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
news_intro,
'<?xml:namespac e prefix = o ns = "urn:schema s-microsoft-com:office:offi ce" />', ''),
'<FONT face="Times New Roman" color=#000000 size=3>', ''),
'<FONT color=#000000>' , ''),
'</FONT>', ''),
'<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt">', ''),
'</P>', ''),
'<o:p>', ''),
'</o:p>', ''),
'<SPAN lang=FR style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-ansi-language: FR">', ''),
'<SPAN lang=FR style="COLOR: black; mso-ansi-language: FR">', ''),
'</SPAN>', '');
You may also want to do this by PHP, so check "strip_tags " function.

Hilarion