So I have a problem. I have a MS SQL database and in it are fields (TEXT) that contain certain characters I'd like to either replace or delete. I orginally tried the following in SQL Query Analyzer:
SET ProductDescription = REPLACE(LTRIM(RTRIM(ProductDescription)), '©', '©')
But that didn't work because MS SQL can't use the replace command on a TEXT field. In the above example I'm trying to replace the copyright symbol with the HTML equivalent. Anyhoo... I Googled my problem and found this forum and another thread with someone that wanted to do something similar. He ran into my same problem using the REPLACE function on a TEXT table in MS SQL. Someone post the code below for him (he wanted to replace one URL with another URL).
Set oConn = Server.CreateObject("ADODB.Connection")
SQL = "select * from table1;"
Set oRS = oConn.Execute(SQL)
Do while not oRS.EOF
SQL2 = "UPDATE table1 SET URLs = '" & Replace(oRS("URLs"),"http://www.bad.com", "http://www.good.com") & "';"
Set oRS = Nothing
Set oRS = Nothing
So... my question is... can some help me with the above code? I'm wise enough to figure out how to replace the URLs above with my characters I want replaced as well as changing his tables to my tables (yea... like that's smart) :)
I'm wondering if I can past the above into a blank ASP page, change the connection settings, and it will automatically run against my database as soon as I load the page?
If the above is true, can someone show me how to change the above code to connect to my database? Lets assume the server IP is 220.127.116.11, database is database1, login is login1, and password in password1.
Can I use something like I'm already using for one of my applications?
Dim db_database, db_Username, db_password, db_server
db_database = "database1"
db_username = "login1"
db_password = "password1"
db_server = "18.104.22.168"
Config_ConnectionString = "Provider=SQLOLEDB; Data Source=" & db_server & "; Initial Catalog=" & db_database & "; User ID="
& db_username & "; Password=" & db_password & "; Network Library=dbmssocn; OLE DB Services=-1;"
Config_DatabaseServer = "SQL"
Any help would REALLY be appreciated. I'm a little lost when it comes to this stuff but even if you can't spell everything out for me if you could at least point me in the right direction I'd be happy :)