instring changes in text field

[travisbrown]

I've got all this html stored in a text field in a SS2K db.

The server people are moving the web fromt end to another server, with a different host name. I need to do an instring update to change all instances of one host name to another. Is my best approach to export to a text file and find and replace, or is there and easy way to do this? I seem to always have trouble with text fields.

 

[Glyndwr]

travis,

I have done a simple mod to the example in BOL (type text and image function into index tetxbox to find]

this shows how to replace a textstrig with another string (oldserver to newserver) it only finds the first occurrence so will need to execute as many times as occurrences

use pubs
go
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
GO
DECLARE @ptrval binary(16)
declare @offset int
 
select @ptrval = TEXTPTR(pr_info) ,@offset = patindex('%(NMB%' , pr_info)
   FROM pub_info pr, publishers p
      WHERE p.pub_id = pr.pub_id 
      AND p.pub_name = 'New Moon Books'

UPDATETEXT pub_info.pr_info @ptrval @offset 3 'mynewserver' 
READTEXT pub_info.pr_info @ptrval 0 0
 
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

Glyndwr

 

[Glyndwr]

travis,

I've just spotted this article, I have not tried it but it may be what you're looking for

http://www.sqlmag.com/Forums/messageview.cfm?catid=8&threadid=8135

Glyndwr

 

[travisbrown]

Cool. Thanks for the pointers.

I also wrote a vbs solution since you can use REPLACE on any string in vbs. It is nice to be able to do it right in SQL though.

<%
Dim rs
Dim rs_numRows

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.ActiveConnection = MM_connFRC_STRING
rs.Source = &quot;SELECT dat_id, dat_rec_id, dat_data FROM dbo.CCM_ContentData&quot;
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 1
rs.Open()
counter = 1
While ((NOT rs.BOF) AND (NOT rs.EOF))

vID = rs.Fields(&quot;dat_id&quot
vData = rs.Fields(&quot;dat_data&quot
vData = REPLACE(vData, &quot;edmssappp1&quot;, &quot;edmsswebp1&quot
vData = REPLACE(vData, &quot;'&quot;, &quot;''&quot
vData = &quot;'&quot; & vData & &quot;'&quot;
vData = cStr(vData)

response.write cStr(counter) & &quot;, &quot;
counter = counter + 1

if isNumeric(vID) = True THEN
call UD(vData,vID)
END IF

rs.movenext
wend
rs.Close()
Set rs = Nothing
FUNCTION UD(vD, vI)

set cmd = Server.CreateObject(&quot;ADODB.Command&quot
cmd.ActiveConnection = MM_connFRC_STRING
response.write vI
'response.end
cmd.CommandText = &quot;UPDATE dbo.CCM_ContentData SET dat_data = &quot; + vD + &quot; WHERE dat_id = &quot; + cStr(vI)
cmd.CommandType = 1
cmd.CommandTimeout = 0
cmd.Prepared = true
cmd.Execute()
END Function
%>