December 9, 2008 at 7:06 am
Ah vendors. Gotta love'em.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 9, 2008 at 7:38 am
Yep...tomorrow's prices with yesterday's technology!
December 10, 2008 at 2:32 am
Dan Epps (12/9/2008)
Yep...tomorrow's prices with yesterday's technology!
Ironic this...prices are tumbling and yesterday's technology is robust!!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 1, 2010 at 1:37 pm
Thanks, this helped me come up with a way to resolve the same issue.
update ITEMS
set description = convert(text, replace(CONVERT(varchar(max),description), 'old string', 'some other string'))
from ITEMS
where description like '%old string%'
Julie
February 2, 2010 at 7:06 pm
Try this solution that first copies the table to a temp table (or table variable), but defines the text column as a varchar(max). Then you can use the REPLACE function with the temp table to find all the occurrences of the server in question and update it with the correct server name. Here is pseudocode:
declare @t1 table (x1 text) -- represents the permanent table
declare @t2 table (x1 varchar(max)) -- represents the temporary table
insert @t1
select replicate('ab',16000)
insert @t2
select x1 from @t1
update @t2 set x1 = replace(x1, 'b', '~')
-- delete the original data in the permanent table
delete from @t1
-- repopulate the modified data into the permanent table
insert @t1
select x1 from @t2
select * from @t1
Hope this helps.
February 2, 2010 at 10:39 pm
Do you even need the temporary table?
You can CAST the text column as VARCHAR(MAX) and do the REPLACE in one UPDATE statement
create table sample (data text)
insert into sample
select 'ababababbabaababababababababbbbbbbbbbbbbbbbbbbbbbbabababababababababababab'
select 'before',* from sample
update sample
SET data = REPLACE(CAST(data AS VARCHAR(MAX)),'b','|')
select 'after',* from sample
drop table sample
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 4, 2010 at 12:49 am
SSCrazy:
Look at Julie Breuntzmann's post just before mine. I think you also need to cast the varchar(max) to a text data type:
update sample
SET data = CAST(REPLACE(CAST(data AS VARCHAR(MAX)),'b','|') AS TEXT)
Do you agree?
--Jeff
February 4, 2010 at 7:11 am
Jeff:
The code I posted was tested before I posted it. It works for the data shown. SQL has no problem doing an implicit conversion from VARCHAR(MAX) to TEXT datatypes. I haven't tested it for strings above 8k though.
I will apologize for stealing Julie's thunder. When I am tired and reading at night I sometimes overlook posts. I thought the REPLACE(CAST()) was short, simple, and easy to read, but it's not really a significant improvement over her post.
By the way, SSCrazy is a "ranking", not my online nickname. Perhaps it should be though....
Regards,
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 4, 2010 at 10:21 am
Bob:
Thanks for your reply.
Sorry I sent you a salutation of "SSCrazy"!
--Jeff
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply