Removing Certain Data

I have some data this is in my database, something like
<p><a href="http://del.icio.us/post?url=http://www.mymerchantaccountblog.com/2011/03/going-back-to-the-dark-ages" title="Going Back to the Dark Ages"><img alt="Delicious" src="/images/social/delicious.gif" width="16" height="16" style="padding-right: 3px" /></a><a href="http://digg.com/submit?phase=2&url=http://www.mymerchantaccountblog.com/2011/03/going-back-to-the-dark-ages" title="Going Back to the Dark Ages"><img alt="Digg This Post" src="/images/social/digg.gif" width="17" height="16" style="padding-right: 3px" /></a><a href="http://www.newsvine.com/_tools/seed&save?u=http://www.mymerchantaccountblog.com/2011/03/going-back-to-the-dark-ages" title="Going Back to the Dark Ages"><img alt="Newsvine" src="/images/social/newsvine.gif" width="13" height="16" style="padding-right: 3px" /></a><a href="http://reddit.com/submit?url=http://www.mymerchantaccountblog.com/2011/03/going-back-to-the-dark-ages" title="Going Back to the Dark Ages"><img alt="Reddit" src="/images/social/reddit.gif" width="15" height="13" style="padding-right: 3px" /></a><a href="http://technorati.com/faves?add=http://www.mymerchantaccountblog.com/2011/03/going-back-to-the-dark-ages" title="Going Back to the Dark Ages"><img alt="Technorati" src="/images/social/technorati.gif" width="16" height="16" style="padding-right: 3px" /></a></p>

Open in new window

I have this code in my database unfortunately.  It is not on all of my posts but the posts that it is in, it differs for each post.

Another example is
<p><a href="http://del.icio.us/post?url=http://www.mymerchantaccountblog.com/2011/02/address-verification-service-fee" title="Address Verification Service Fee"><img alt="Delicious" src="/images/social/delicious.gif" width="16" height="16" style="padding-right: 3px" /></a><a href="http://digg.com/submit?phase=2&url=http://www.mymerchantaccountblog.com/2011/02/address-verification-service-fee" title="Address Verification Service Fee"><img alt="Digg This Post" src="/images/social/digg.gif" width="17" height="16" style="padding-right: 3px" /></a><a href="http://www.newsvine.com/_tools/seed&save?u=http://www.mymerchantaccountblog.com/2011/02/address-verification-service-fee" title="Address Verification Service Fee"><img alt="Newsvine" src="/images/social/newsvine.gif" width="13" height="16" style="padding-right: 3px" /></a><a href="http://reddit.com/submit?url=http://www.mymerchantaccountblog.com/2011/02/address-verification-service-fee" title="Address Verification Service Fee"><img alt="Reddit" src="/images/social/reddit.gif" width="15" height="13" style="padding-right: 3px" /></a><a href="http://technorati.com/faves?add=http://www.mymerchantaccountblog.com/2011/02/address-verification-service-fee" title="Address Verification Service Fee"><img alt="Technorati" src="/images/social/technorati.gif" width="16" height="16" style="padding-right: 3px" /></a></p>

Open in new window


I did not know if there would be a simple query that I could run to remove these codes.  If so, what would be the query?

Thank you!
LVL 29
coreybryantAsked:
Who is Participating?
 
gatorvipCommented:
You should still be able to use regular expressions in Access.

An example is here (a couple posts down):
http://www.tek-tips.com/viewthread.cfm?qid=1441918&page=10


The main advantage to doing it this way is that you wouldn't change the rest of the post (including other HTML coding). The disadvantage is that you would need to adapt the regex for other patterns, if the one posted by OP can change.
0
 
danishaniCommented:
What data do you need to remove?
Or do you want to extract certain data?

HTH,
Daniel
0
 
coreybryantAuthor Commented:
I need to remove the data above.  As you can see, the data is changed for each posts.  I did not know if there was some type of query to run and using something like "*" in the areas that are different.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
danishaniCommented:
Remove the whole string out of a field/ or in code?
Where does it come from?
What data should this have bee normaly?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'll have to be more specific. You can certainly delete data, but you must have some sort of defining parameters to do so. For example, if you want to remove everything in a column between the "<p>" and "</p>" strings you can write code to do that.
0
 
coreybryantAuthor Commented:
The codes that I need to be removed are
<p><a href="http://del.icio.us/post?url=http://www.mymerchantaccountblog.com/2011/03/going-back-to-the-dark-ages" title="Going Back to the Dark Ages"><img alt="Delicious" src="/images/social/delicious.gif" width="16" height="16" style="padding-right: 3px" /></a><a href="http://digg.com/submit?phase=2&url=http://www.mymerchantaccountblog.com/2011/03/going-back-to-the-dark-ages" title="Going Back to the Dark Ages"><img alt="Digg This Post" src="/images/social/digg.gif" width="17" height="16" style="padding-right: 3px" /></a><a href="http://www.newsvine.com/_tools/seed&save?u=http://www.mymerchantaccountblog.com/2011/03/going-back-to-the-dark-ages" title="Going Back to the Dark Ages"><img alt="Newsvine" src="/images/social/newsvine.gif" width="13" height="16" style="padding-right: 3px" /></a><a href="http://reddit.com/submit?url=http://www.mymerchantaccountblog.com/2011/03/going-back-to-the-dark-ages" title="Going Back to the Dark Ages"><img alt="Reddit" src="/images/social/reddit.gif" width="15" height="13" style="padding-right: 3px" /></a><a href="http://technorati.com/faves?add=http://www.mymerchantaccountblog.com/2011/03/going-back-to-the-dark-ages" title="Going Back to the Dark Ages"><img alt="Technorati" src="/images/social/technorati.gif" width="16" height="16" style="padding-right: 3px" /></a></p>

Open in new window

for example.  

The data is just should be deleted.  I was told that I would replace this code in the ASP code instead.  

Since the code varies on each post, I did not know if I can use things like  (<p><a href="http://del.icio.us/post*style="padding-right: 3px" /></a></p>) to remove the code form these table)with the asterisk representing all the text between <p><a href="http://del.icio.us/post and style="padding-right: 3px" /></a></p>
0
 
danishaniCommented:
So if I understand you correctly, you want to strip out the HTML tags. Check below thread;
http://msgroups.net/microsoft.public.access.queries/how-to-remove-html-tags-from-a-field-in-MSACCESS

HTH,
Daniel
0
 
coreybryantAuthor Commented:
No necessarily the HTML tags.  
 
<p>This fee can actually be two-fold: a merchant can be charged a transaction 
fee from the <span title="merchant account provider">merchant account provider</span> and the 
<a href="http://www.mymerchantaccountblog.com/electronic-payment-gateways" title="electronic payment gateway">electronic payment gateway</a>.&nbsp; 
Since this fee is a flat rate and not a percentage of the sale, this is where it 
can hurt some merchants - especially when the transaction is only a few dollars.</p>
<h3>Internet Merchant Accounts</h3>
<p>If your business is online (<a href="http://www.milehighmerchantaccounts.com/internet-merchant-account" title="Internet Merchant Account">Internet 
Merchant Account</a>) or
<a href="http://www.milehighmerchantaccounts.com/telephone-mail-order-merchant-account" title="Telephone / Mail Order Merchant Account">
Telephone / Mail Order Merchant Account</a> (<acronym title="Telephone / Mail Order Merchant Account">MOTO</acronym>), you will need an 
<a href="http://www.mymerchantaccountblog.com/electronic-payment-gateways" title="electronic payment gateway">electronic payment gateway</a>.&nbsp; Some
<a href="http://www.mymerchantaccountblog.com/electronic-payment-gateways" title="electronic payment gateways">electronic payment gateways</a> 
might give you a certain number of free transactions and then charge the 
merchant anywhere from 5&cent; - 50&cent; per  transaction.&nbsp; Other
<a href="http://www.mymerchantaccountblog.com/electronic-payment-gateways" title="electronic payment gateways">electronic payment gateways</a> 
(like the
<a href="http://www.milehighmerchantaccounts.com/quantum-gateway" title="Quantum Payment Gateway">
Quantum Payment Gateway</a>), does not charge a transaction fee.&nbsp;  </p>
<p>When applying for a merchant account, the merchant needs to know what 
<a href="http://www.mymerchantaccountblog.com/electronic-payment-gateways" title="electronic payment gateways">payment 
gateways</a> are compatible with the <span title="merchant account provider">provider</span>.&nbsp; 
Sometimes, when applying for an <a href="http://www.milehighmerchantaccounts.com/internet-merchant-account" title="Internet Merchant Account">Internet 
Merchant Account</a>, the merchant might need to sign another contract with the 
payment gateway provider.&nbsp; Before signing the contracts, make sure to see 
what the transaction cost(s) will be.&nbsp; The merchant account provider might 
charge you 25&cent;, the 
<a href="http://www.mymerchantaccountblog.com/electronic-payment-gateways" title="electronic payment gateway">electronic payment gateway</a> 
might charge you an additional 5&cent; - 50&cent; per  transaction, and the 
merchant probably also be charged an Address Verification Service Fee (<acronym title="Address Verification Service Fee">AVS</acronym>) 
which usually is 5&cent; - 10&cent; per transaction.</p>
<p>This means that the merchant could potentially be charged anwhere from 35&cent; to 
85&cent; on the transaction.&nbsp; So on a $5.00 transaction, the merchant could 
be paying around 10% just on this fee alone.</p>
<p><a href="http://del.icio.us/post?url=http://www.mymerchantaccountblog.com/2011/02/transaction-fee" title="Transaction Fee"><img alt="Delicious" src="/images/social/delicious.gif" width="16" height="16" style="padding-right: 3px" /></a><a href="http://digg.com/submit?phase=2&url=http://www.mymerchantaccountblog.com/2011/02/transaction-fee" title="Transaction Fee"><img alt="Digg This Post" src="/images/social/digg.gif" width="17" height="16" style="padding-right: 3px" /></a><a href="http://www.newsvine.com/_tools/seed&save?u=http://www.mymerchantaccountblog.com/2011/02/transaction-fee" title="Transaction Fee"><img alt="Newsvine" src="/images/social/newsvine.gif" width="13" height="16" style="padding-right: 3px" /></a><a href="http://reddit.com/submit?url=http://www.mymerchantaccountblog.com/2011/02/transaction-fee" title="Transaction Fee"><img alt="Reddit" src="/images/social/reddit.gif" width="15" height="13" style="padding-right: 3px" /></a><a href="http://technorati.com/faves?add=http://www.mymerchantaccountblog.com/2011/02/transaction-fee" title="Transaction Fee"><img alt="Technorati" src="/images/social/technorati.gif" width="16" height="16" style="padding-right: 3px" /></a></p>

Open in new window

and
 
<p>A batch fee is charged by the merchant account provider when a group of 
transactions is sent to the merchant account provider to be processed.&nbsp; For 
a
<a href="http://www.milehighmerchantaccounts.com/retail-merchant-account" title="retail merchant account">
retail merchant account</a>, this is usually done at the end of business.&nbsp; 
For an
<a href="http://www.milehighmerchantaccounts.com/internet-merchant-account" title="Internet merchant account">
Internet merchant account</a>, this is usually done by the
<a href="http://www.mymerchantaccountblog.com/electronic-payment-gateways" title="electronic payment gateway">
electronic payment gateway</a>, once a day and can usually be set by the
<a href="http://www.milehighmerchantaccounts.com/quantum-gateway/virtual-terminal" title="virtual terminal">
virtual terminal</a>. </p>
<p>A merchant can close a set of transactions at any time, but if a merchant 
waits more than three business days after the sales transaction - the merchant 
account provider might downgrade some of those transactions to the non-qualified 
rate and the merchant risks losing the authorization.</p>
<p>Unlike the
<a href="http://www.mymerchantaccountblog.com/2011/01/ach-fee" title="ACH fee">
ACH fee</a>, this fee is usually charged once a day - when the transactions are 
closed, even on the weekends.&nbsp; So in a 30 day month with a 25&cent; fee, the 
merchant will see a charge of $7.50.&nbsp; In the United States, most merchants 
will not see this fee.&nbsp;
<a href="http://www.merchantaccountlab.com/high-risk-merchant-account" title="High risk merchants">
High risk merchants</a> and merchants in other countries will usually see this 
fee.</p>
	<p>By locating a merchant account provider that does not charge this fee, a 
	merchant could save almost $100.00 a year!</p>
<p><a href="http://del.icio.us/post?url=http://www.mymerchantaccountblog.com/2011/02/batch-fee" title="Batch Fee"><img alt="Delicious" src="/images/social/delicious.gif" width="16" height="16" style="padding-right: 3px" /></a><a href="http://digg.com/submit?phase=2&url=http://www.mymerchantaccountblog.com/2011/02/batch-fee" title="Batch Fee"><img alt="Digg This Post" src="/images/social/digg.gif" width="17" height="16" style="padding-right: 3px" /></a><a href="http://www.newsvine.com/_tools/seed&save?u=http://www.mymerchantaccountblog.com/2011/02/batch-fee" title="Batch Fee"><img alt="Newsvine" src="/images/social/newsvine.gif" width="13" height="16" style="padding-right: 3px" /></a><a href="http://reddit.com/submit?url=http://www.mymerchantaccountblog.com/2011/02/batch-fee" title="Batch Fee"><img alt="Reddit" src="/images/social/reddit.gif" width="15" height="13" style="padding-right: 3px" /></a><a href="http://technorati.com/faves?add=http://www.mymerchantaccountblog.com/2011/02/batch-fee" title="Batch Fee"><img alt="Technorati" src="/images/social/technorati.gif" width="16" height="16" style="padding-right: 3px" /></a></p>

Open in new window

As you can see each of these has a code at the end
<p><a href="http://del.icio.us/post?url=http://www.mymerchantaccountblog.com/2011/02/transaction-fee" title="Transaction Fee"><img alt="Delicious" src="/images/social/delicious.gif" width="16" height="16" style="padding-right: 3px" /></a><a href="http://digg.com/submit?phase=2&url=http://www.mymerchantaccountblog.com/2011/02/transaction-fee" title="Transaction Fee"><img alt="Digg This Post" src="/images/social/digg.gif" width="17" height="16" style="padding-right: 3px" /></a><a href="http://www.newsvine.com/_tools/seed&save?u=http://www.mymerchantaccountblog.com/2011/02/transaction-fee" title="Transaction Fee"><img alt="Newsvine" src="/images/social/newsvine.gif" width="13" height="16" style="padding-right: 3px" /></a><a href="http://reddit.com/submit?url=http://www.mymerchantaccountblog.com/2011/02/transaction-fee" title="Transaction Fee"><img alt="Reddit" src="/images/social/reddit.gif" width="15" height="13" style="padding-right: 3px" /></a><a href="http://technorati.com/faves?add=http://www.mymerchantaccountblog.com/2011/02/transaction-fee" title="Transaction Fee"><img alt="Technorati" src="/images/social/technorati.gif" width="16" height="16" style="padding-right: 3px" /></a></p>

Open in new window

and
<p><a href="http://del.icio.us/post?url=http://www.mymerchantaccountblog.com/2011/02/batch-fee" title="Batch Fee"><img alt="Delicious" src="/images/social/delicious.gif" width="16" height="16" style="padding-right: 3px" /></a><a href="http://digg.com/submit?phase=2&url=http://www.mymerchantaccountblog.com/2011/02/batch-fee" title="Batch Fee"><img alt="Digg This Post" src="/images/social/digg.gif" width="17" height="16" style="padding-right: 3px" /></a><a href="http://www.newsvine.com/_tools/seed&save?u=http://www.mymerchantaccountblog.com/2011/02/batch-fee" title="Batch Fee"><img alt="Newsvine" src="/images/social/newsvine.gif" width="13" height="16" style="padding-right: 3px" /></a><a href="http://reddit.com/submit?url=http://www.mymerchantaccountblog.com/2011/02/batch-fee" title="Batch Fee"><img alt="Reddit" src="/images/social/reddit.gif" width="15" height="13" style="padding-right: 3px" /></a><a href="http://technorati.com/faves?add=http://www.mymerchantaccountblog.com/2011/02/batch-fee" title="Batch Fee"><img alt="Technorati" src="/images/social/technorati.gif" width="16" height="16" style="padding-right: 3px" /></a></p>

Open in new window

It is these codes that I would like to be removed.

They are different on each post unfortunately.  I did not know if there would be some type of query I can run to locate something that starts with
<p><a href="http://del.icio.us/post
and ends with
<img alt="Technorati" src="/images/social/technorati.gif" width="16" height="16" style="padding-right: 3px" /></a></p>
and then it is removed.  I guess something similar when you are searching in Windows, you search for a file like mymerchant*blog.txt and it will bring up any files with the file name that starts with mymerchant and ends with blog.txt
0
 
danishaniCommented:
Ok, so you need to erase all the text after the first href and before the <img>?

Extracting only the href and <img> tag?

HTH,
Daniel
0
 
coreybryantAuthor Commented:
Well there could be an <img> in the post.  So I don't want to remove <img>  I need to see if there is a way to remove
<p><a href="http://del.icio.us/post
and ends with
<img alt="Technorati" src="/images/social/technorati.gif" width="16" height="16" style="padding-right: 3px" /></a></p>

This is at the end of maybe 50 posts.  But
<p><a href="http://del.icio.us/post
and ends with
<img alt="Technorati" src="/images/social/technorati.gif" width="16" height="16" style="padding-right: 3px" /></a></p>
is the same.  

As you can see in the examples, the differences would be specific to the posts
0
 
gatorvipCommented:
>>
<p><a href="http://del.icio.us/post
and ends with
<img alt="Technorati" src="/images/social/technorati.gif" width="16" height="16" style="padding-right: 3px" /></a></p>
<<

It could be pretty simple, since you know what the starting and ending text strings are. That's where you can use regular expressions. The implementation, however, is platform specific.
SQL Server - Regex.Match
MySQL - REGEXP
Oracle - REGEXP_LIKE

In all cases, you're looking for the following string:
"<p><a href=\"http://del.icio.us/post"   followed by some text followed by "<img alt=\"Technorati\" src=\"/images/social/technorati.gif\" width=\"16\" height=\"16\" style=\"padding-right: 3px\" /></a></p>"   (note that all the interior quotes are escaped)
0
 
aikimarkCommented:
1. Since this is a post, wouldn't this mis-form the HTML?
2. Does this mean that sometimes the end is an <img tag and sometimes the end is in some other tag?
0
 
coreybryantAuthor Commented:
It is an MS Access database.  It is in the Data table with a fieldname Text
0
 
aikimarkCommented:
@coreybryant

Were you responding to me?
0
 
coreybryantAuthor Commented:
I would say both, since gatorvip provided some type of code and it was lost when the post was moved that it was MS Access.  

The code is the same in the beginning and at the end.  
0
 
aikimarkCommented:
Please reread my earlier post.  In the first point, I'm raising the prospect of changing the html in your Access table to the point where the HTML will be broken when you use the data.

In the second point, I'm trying to understand the diversity/scope of the strings we might encounter in your table.  From earlier comments, this might be part of an img tag an sometimes not part of an img tag.
0
 
coreybryantAuthor Commented:
I was able to hire someone to get the job done.  I did point them to both of these posts but they had already removed them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.