?
Solved

Removing Certain Data

Posted on 2011-05-02
19
Medium Priority
?
188 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:coreybryant
  • 7
  • 4
  • 3
  • +2
17 Comments
 
LVL 12

Expert Comment

by:danishani
ID: 35508535
What data do you need to remove?
Or do you want to extract certain data?

HTH,
Daniel
0
 
LVL 29

Author Comment

by:coreybryant
ID: 35508555
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
 
LVL 12

Expert Comment

by:danishani
ID: 35508626
Remove the whole string out of a field/ or in code?
Where does it come from?
What data should this have bee normaly?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 85
ID: 35508714
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
 
LVL 29

Author Comment

by:coreybryant
ID: 35509010
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
 
LVL 12

Expert Comment

by:danishani
ID: 35514219
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
 
LVL 29

Author Comment

by:coreybryant
ID: 35516333
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
 
LVL 12

Expert Comment

by:danishani
ID: 35692923
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
 
LVL 29

Author Comment

by:coreybryant
ID: 35694761
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
 
LVL 20

Assisted Solution

by:gatorvip
gatorvip earned 2000 total points
ID: 35701300
>>
<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
 
LVL 46

Expert Comment

by:aikimark
ID: 35702242
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
 
LVL 29

Author Comment

by:coreybryant
ID: 35702377
It is an MS Access database.  It is in the Data table with a fieldname Text
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35702478
@coreybryant

Were you responding to me?
0
 
LVL 29

Author Comment

by:coreybryant
ID: 35706874
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35707056
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
 
LVL 20

Accepted Solution

by:
gatorvip earned 2000 total points
ID: 35707233
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
 
LVL 29

Author Closing Comment

by:coreybryant
ID: 35715721
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question