Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

help with sql query please

Posted on 2007-11-24
13
Medium Priority
?
351 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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.…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Screencast - Getting to Know the Pipeline
Suggested Courses

972 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