Solved

help with sql query please

Posted on 2007-11-24
13
342 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

822 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