Custom script to remove special characters

midoriweb · January 12th, 2007, 07:39 AM

Hi everyone,

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:

Code:

UPDATE Products_Descriptions
SET ProductDescription = REPLACE(LTRIM(RTRIM(ProductDescription)), 'Â©', '&copy;')

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

Code:

Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open Application("conn")

    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") & "';"
    oConn.Execute(SQL2)
    oRS.Movenext
    Loop
    oRS.Close
    Set oRS = Nothing
    oConn.Close
    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 1.2.3.4, database is database1, login is login1, and password in password1.

Can I use something like I'm already using for one of my applications?

Code:

<%

    Dim db_database, db_Username, db_password, db_server
    db_database = "database1"
    db_username = "login1"
    db_password = "password1"
    db_server   = "1.2.3.4"
    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 :)

midoriweb · January 12th, 2007, 07:49 AM

Would the following code work? It's my best personal attempt! :)

Also... would I just upload that to my server and run it from a web browser?

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=mydatabase "
%>

<%
    SQL = "select * from Products_Descriptions;"

    Set oRS = cnn.Execute(SQL)
    Do while not oRS.EOF
    SQL2 = "UPDATE Products_Descriptions SET ProductDescription = '" & Replace(oRS("ProductDescription"),"Â©", "&copy;") & "';"
    cnn.Execute(SQL2)
    oRS.Movenext
    Loop
    oRS.Close
    Set oRS = Nothing
    cnn.Close
    Set oRS = Nothing

%>

midoriweb · January 12th, 2007, 08:05 AM

Decided to run it and see what happened... the following error posted to the page:

Microsoft OLE DB Provider for SQL Server error '80040e31'

Timeout expired

/SQL_update.asp, line 14