SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Orange County, CA, Unites States
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Running a SQL query to replace certain characters in a table

    We have a database that contains many characters and symbols we no longer want (or that we need to replace with something else). For example... We have some records in a database that contain the copyright symbol:



    And we want to replace that with:

    ©

    Our table is titled Product_Descriptions and the column we want to run this query on is ProductDescription.

    I've already searched and I found this:

    UPDATE <table>
    SET name = REPLACE(LTRIM(RTRIM(name)), '', '&copy;')

    That should work right? But what do I enter to run this on my specific table/column? I know I have to replace the <table> from above with my own input... but can someone guide me in the right direction. I'm a noob when it's comes to SQL queries.

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Orange County, CA, Unites States
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry... I should have clarified... this on on MS SQL.

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Orange County, CA, Unites States
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    An update...

    I found the following command to run against my database:

    UPDATE Products_Descriptions
    SET ProductDescription = REPLACE(LTRIM(RTRIM(ProductDescription)), '&#169;', '&copy;')

    However... when I try to run it it throws the following error:

    Server: Msg 8116, Level 16, State 2, Line 1
    Argument data type text is invalid for argument 1 of rtrim function.

    Any ideas?

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Orange County, CA, Unites States
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried:

    UPDATE Products_Descriptions
    SET ProductDescription = REPLACE(ProductDescription,'&#169;','&copy;')

    But got this error:

    Server: Msg 8116, Level 16, State 1, Line 1
    Argument data type text is invalid for argument 1 of replace function.

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Orange County, CA, Unites States
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ASP Script to replace certain characters from a database with another

    Ok... so I originally wanted to do this using MS SQL's Query analizer but it appears the REPLACE function doesn't work in TEXT fields. So... next idea I found was to create a simple ASP script to run against the DB. This is what I found:

    ********************************************

    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

    ******************************************

    Now... the person who gave that example above wanted to replace one URL in their database with another URL. So I have 2 questions.

    1.) Will that script above replace just the characters I want replaced or will it effect the entire column of the table I'm editing? For example, if the database has "This is Mike from Wazoo" and I run a script to replace Mike with Tim, would the script change to "This is Tim from Wazoo"? I just want to make sure it won't replace the entire field. I want only the text I say to be changed... everything else stays the same.

    2.) Can someone modify the above code to work with my database (I don't know enough ASP to mess around with it)? My table I need to edit is Products_Descriptions and the column I need to modify is ProductDescription. Lets say my server IP for the DB connection is 1.2.3.4 and my datbase is: database1, login is: login1, and pass is: pass1. For replacing characters I can figure that one out

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Orange County, CA, Unites States
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've done some more research and it appears since the database column is of type TEXT I can't run the replace function.

    So... next best thing is to write a custom ASP Script.

    If anyone can help me I'd really appreciate it
    Last edited by r937; Jan 12, 2007 at 02:05.

  7. #7
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Orange County, CA, Unites States
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    
    %>

  8. #8
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Orange County, CA, Unites States
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •