Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

help with sql query please

Posted on 2007-11-24
13
Medium Priority
?
352 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
  • 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 2000 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 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