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

[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_article, '<FONT
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

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

....

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:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />', '' ),
'<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:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />', ''),
'<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