Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 13th, 2006, 11:03 AM
Authorized User
 
Join Date: Aug 2006
Location: , NC, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Global Search&Replace Text in all fields in sql db

Hello,

I am fairly new to SQL, using SQL Server 2000 running on Windows Server 2003. I have been using EMS MS SQL Manager Lite to navigate around and edit the databases I have inherited - worked well so far until I take some SQL courses soon.

Problem: I need to search through multiple fields of this table which contains 456 records so that I can replace one URL with the correct URL. OTherwise, I will have to manually go in and check and update all records. Or what I have done in the past is to create a live ODBC data connection from Excel and then I can review and identify what I need to change prior to changing it in sql database itself. Kind of cumbersome.

Can anyone out there can tell me about a script to execute that would search and replace one URL in all fields of this one table in this particular database?

Also, how and where would I would run that script?

Thanks.
  #2 (permalink)  
Old September 13th, 2006, 11:58 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Well if it was one column you were working with you could do something like this:

Update [table] set [column] = value where [column] = oldvalue

Problem is, if i understand you correctly, you have about 500 rows of data and this URL you are talking about is contained in multiple fields through these 500 rows, correct?

You can use TSQL's Replace() function which has 3 parameters

Replace([string to search], [oldvalue], [newvalue])

You are probably going to have to use a cursor (shudder) to loop through your columns row by row.


"The one language all programmers understand is profanity."
  #3 (permalink)  
Old September 13th, 2006, 01:08 PM
Authorized User
 
Join Date: Aug 2006
Location: , NC, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK. So I am in SQL query analyzer on the server, and I tried the following:

SELECT dbo.tablename
REPLACE ('column1','http://www.URL.com/incorrect/','http://www.good.URL.com/')
GO

-and I attempt to execute. It tells me:
Line 1: Incorrect syntax near 'column1'.


AND THEN, I tried this:

SELECT column1
FROM dbo.table
Replace ([table], [http://www.url.com/bad/], [http://www.good.url.com/])
GO


And then I get error messages:

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '/'.
Server: Msg 132, Level 15, State 1, Line 3
The label 'http' has already been declared. Label names must be unique within a query batch or stored procedure.

I don't know. What do you think? Did I mistype some command? Any further suggestions? Thanks.

BuddyZ
  #4 (permalink)  
Old September 13th, 2006, 01:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

There are a couple things wrong with your attempt.

REPLACE is a function which means it returns a value. The value it returns the result when the first argument expression is searched for occurances of the second, and all such occurances are replaced by the third.

You don't SELECT a table; you SELECT columns (or expressions or column expressions) FROM a table.

If you want to change the value of columns in a row, the SELECT statement won't do that; you'll need to UPDATE the rows appropriately.

So, if the column named 'column1' in your table to is be searched for the "bad" URL and replaced with the "good" one, your SQL statment should be along the lines of:

    UPDATE yourtable
        SET column1 = REPLACE(column1, 'http://www.URL.com/incorrect/', 'http://www.good.URL.com/')

and this will change the column1 value of every row. If the column value contains the "bad" URL, those characters will be replaced with the "good" one; otherwise, the REPLACE function will just return the original (unchanged) value of the column. Thus, rows which don't contain the "bad" value are simply unchanged.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #5 (permalink)  
Old September 13th, 2006, 02:00 PM
Authorized User
 
Join Date: Aug 2006
Location: , NC, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello,
Thanks for the explicit explanations and instructions - that helped.

When I input the corrected query in SQL query analyzer per your instructions and click "parse" to check it, it says:

The command(s) completed successfully.

HOWEVER, when I attempt to execute it, I get this message:

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

The column/field I am trying to search and replace in this exercise is indeed a text field which contains numerous URL occurrences. So why would it not recognize a URL?

After I got this error message above, I even tried it with some of the standard <a href. . .> HTML tag coding surrounding the URLs to see if that would help, and no change. Alas.

Thanks.

BuddyZ
  #6 (permalink)  
Old September 13th, 2006, 02:14 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Uh oh. You should have said you were attempting to manipulate TEXT columns.

I'm afraid the REPLACE function (and many other string functions) will not work with the TEXT datatype. This datatype is not the same, nor is it processed the same, as the other more natural 'native' datatypes in SQL Server.

I'm not aware of any easy way to do what you want, given that datatype, I'm afraid.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #7 (permalink)  
Old September 13th, 2006, 02:36 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

You could write a simple script in classic ASP like:

    
Code:
Set oConn = Server.CreateObject("ADODB.Connection")
Code:
    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


  #8 (permalink)  
Old September 13th, 2006, 02:59 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

I agree with Jeff, I know of no easy way to work with the TEXT datatype, but Rich's idea should get the job done for you. You will have to forgive the vagueness of my posts, I try to encourage people to learn and play around with concepts they are not familiar with, the benifits of doing so are alot greater then me simply stating how to do something; my appologies.

"The one language all programmers understand is profanity."
  #9 (permalink)  
Old September 13th, 2006, 03:09 PM
Authorized User
 
Join Date: Aug 2006
Location: , NC, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Doug,
Every response is most appreciated! And I have learned a lot from yours. I will try all suggestions, believe me. It is definitely helping me to learn more about sql and asp and using them. So thanks.
  #10 (permalink)  
Old September 13th, 2006, 03:20 PM
Authorized User
 
Join Date: Aug 2006
Location: , NC, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

AND the good news is, that I am able to work the queries on some of the other fields in the table, just ran it on one of the VARCHAR type fields, so that helps.

Thanks!

BuddyZ




Similar Threads
Thread Thread Starter Forum Replies Last Post
Search and Replace a text in XML file AyatKh XML 17 May 23rd, 2012 09:05 PM
How to replace db column name with Global variable nesrine C# 0 March 5th, 2007 10:06 AM
How can i replace db column name with Global varia nesrine ASP.NET 2.0 Professional 0 March 5th, 2007 10:05 AM
replace fillin with ask fields & add references mariatherp Beginning VB 6 1 December 15th, 2004 06:12 AM
RTF Search & Replace PC User Access 0 June 16th, 2004 01:46 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.