Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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.
Reply With Quote
  #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."
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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


Reply With Quote
  #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."
Reply With Quote
  #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.
Reply With Quote
  #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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:18 PM.


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