Solved

help with sql query please

Posted on 2007-11-24
13
343 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

831 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