Solved

help with sql query please

Posted on 2007-11-24
13
344 Views
Last Modified: 2010-03-20
hi,

I hope this can be done with a single query, in my db which is access, there is a problem, some of the records in some columns contain a single "</td>" as value, is it possible to have a query go through all records and all columns and simply change "</td>" to ""

the entire content of the field has to match exactly, not a partial match, as some of the fields do contain the string inside as part of their value...

please let me know how to do this exactly, I can run a query on db without problem, but have no idea how this query might look...

0
Comment
Question by:quantumrider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 2

Expert Comment

by:Matt Grofsky
ID: 20343241
SELECT Replace(Column, '</td>', '') as TDReplaced
From Table

The replace function will do the trick for ya its a life saver, but as an FYI, its not something you want to use on millions of records, its a bit of a hog.

http://msdn2.microsoft.com/En-US/library/ms186862.aspx

0
 

Author Comment

by:quantumrider
ID: 20343352
this is an access db, getting 'Undefined function 'Replace' in expression.'

SELECT Replace(Column, '</td>', '') as TDReplaced From Cars
0
 

Author Comment

by:quantumrider
ID: 20343428
I am trying this query, to do it one column at a time

UPDATE Cars
SET Miles = ''
WHERE Miles = '</td>'

I can see the records with that string in the Miles column yet the result I get is 0 records affected...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Accepted Solution

by:
Matt Grofsky earned 500 total points
ID: 20343661
Access 200 has some bugs with replace

Paste this code into a new Module and Save.

Public Function QReplace(strIn As String, strOld As String, strNew As
String) As String
    QReplace = Replace(strIn, strOld, strNew)
End Function



Then you can use this query to do your Update.

UPDATE cars SET cars.columnname= QReplace([Cars].[columnname],"</td>","");
0
 

Author Comment

by:quantumrider
ID: 20343713
where is the new Module? I've never used that before....
0
 

Author Comment

by:quantumrider
ID: 20343720
I only use it on the server from asp
0
 
LVL 2

Expert Comment

by:Matt Grofsky
ID: 20343759
its creating a custom function so you can replace unstances of </td> in your database

You will need to open the database in access and create a module there, you won't be able to create it via asp
0
 
LVL 2

Expert Comment

by:Matt Grofsky
ID: 20343772
your other option is to replace the td tags after you run the query and use the replace function in asp
0
 

Author Comment

by:quantumrider
ID: 20343797
I'm already doing that... after getrows I let it it rip and get those /td's out... but would prefer to clean up db
0
 
LVL 2

Expert Comment

by:Matt Grofsky
ID: 20343881
One way to clean up the db in a way that might be easy for you is when you select and replace using asp update the record in the db after you do the replace in asp
0
 
LVL 19

Expert Comment

by:LordOfPorts
ID: 20344158
Your query should work. There is probably another character(s) besides </td>, e.g. a single space before or after </td>, in which case you could ajust the query:

UPDATE Cars
SET Miles = ''
WHERE Miles = ' </td>'
0
 

Author Comment

by:quantumrider
ID: 20344554
you are right, that was the problem... what should I do with this question?
0
 
LVL 2

Expert Comment

by:Matt Grofsky
ID: 20344798
I misread your question, give it to quantumrider, I thought you had records that contained data other than </td> along side it and wanted to remove the </td> from the column but leave the other data intact.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 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