asked on

help with sql query please

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...

Matt Grofsky

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

quantumrider

ASKER

this is an access db, getting 'Undefined function 'Replace' in expression.'

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

quantumrider

ASKER

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...

ASKER CERTIFIED SOLUTION

Matt Grofsky

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

quantumrider

ASKER

where is the new Module? I've never used that before....

quantumrider

ASKER

I only use it on the server from asp

Matt Grofsky

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

Matt Grofsky

your other option is to replace the td tags after you run the query and use the replace function in asp

quantumrider

ASKER

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

Matt Grofsky

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

LordOfPorts

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>'

quantumrider

ASKER

you are right, that was the problem... what should I do with this question?

Matt Grofsky

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.