Replacing multiple occurrences of a string in a text column

Question

Replacing multiple occurrences of a string in a text column

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

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]

Dan Epps Ten Centuries Points: 1324 More actions · Answer 2

Yep...tomorrow's prices with yesterday's technology!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Dan Epps (12/9/2008)
Yep...tomorrow's prices with yesterday's technology!

Ironic this...prices are tumbling and yesterday's technology is robust!!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Julie Breutzmann Hall of Fame Points: 3637 More actions · Answer 4

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

JLSSCH Ten Centuries Points: 1013 More actions · Answer 5

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.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

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

JLSSCH Ten Centuries Points: 1013 More actions · Answer 7

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

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

JLSSCH Ten Centuries Points: 1013 More actions · Answer 9

Bob:

Thanks for your reply.

Sorry I sent you a salutation of "SSCrazy"!

--Jeff