Fast replace list of words in string

Question

Post reply

Fast replace list of words in string

Jules Bonnot

SSCertifiable

Points: 5488
More actions
March 20, 2010 at 11:21 am

#212847

i want to replace all words in the string which are in the table with a % symbol.
declare @tab table (ID int identity, value varchar(1000))
declare @string varchar(4000)
set @string = 'here is some text for testing this code'
insert @tab
select 'here' union
select 'is' union
select 'for' union
select 'this'
-- so result should be '% % some text % testing % code'
thanks for all help and tips.
Jules
www.sql-library.com[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 1

this works but i dont like to use a loop and was hoping there might be some magic with a number table i could try....

declare @tab table (ID int identity, value varchar(1000))

declare @result varchar(4000), @word varchar(1000),@i int, @no int

declare @string varchar(4000)

set @string = 'here is some text for testing this code'

set @string = ' ' + @string + ' '

set @i = 1

insert @tab

select 'here' union

select 'is' union

select 'for' union

select 'this'

set @no = SCOPE_IDENTITY()

set @word = ''

while @i <= @no

begin

select @word = value

from @tab

where ID = @i

set @string = replace(@string, ' '+ @word+ ' ', ' % ')

set @i = @i +1

end

select ltrim(rtrim(@string))

www.sql-library.com[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 2

Needs some refining, but essentially sound, and very fast:

DECLARE @string VARCHAR(4000);

SET @string = SPACE(1) + 'here is some text for testing this code';

WITH ToRemove (word)

AS (

SELECT ' here ' COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS UNION ALL

SELECT ' is ' COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS UNION ALL

SELECT ' for ' COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS UNION ALL

SELECT ' this ' COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS

)

SELECT @string =

REPLACE

(

@string COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

ToRemove.word COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

SPACE(1) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS

)

FROM ToRemove

WHERE CHARINDEX

(

ToRemove.word COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

@string COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS

) > 0;

SELECT @string;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

This will let you replace a set of words with whatever replacements you choose.

declare @tab table (findWord varchar(50) primary key, replaceWord varchar(50))

declare @result varchar(4000)

declare @string varchar(4000)

set @string = 'here is some text for testing this code'

set @string = ' '+@string+' '

insert @tab

select 'here','%' union

select 'is','%' union

select 'for','%' union

select 'this','%'

----------------------------------------------------------

select @string = replace(@string,' '+findWord+' ',' '+replaceWord+' ')

from @tab

----------------------------------------------------------

select ltrim(rtrim(@string))

Edited to account for spaces between words, so that strings within words aren't replaced.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Paul White SSC Guru Points: 150467 More actions · Answer 4

Thanks, that is tidier. I would probably replace those UNIONs with UNION ALLs though. Oh, and it isn't important with such a small number of REPLACEs, but those COLLATEs really speed things up on larger sets. Windows collations can be 30x slower than SQL or BINary collations, due to the linguistic comparison and sorting rules.

edit: more detail!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

No recursive CTE this time, Paul? 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Paul White SSC Guru Points: 150467 More actions · Answer 6

The Dixie Flatline (3/20/2010)
No recursive CTE this time, Paul? 😉

Recursive CTEs are sooooo yesterday. Everything involves hierarchyid today 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

I'm sure there must be a spatial solution too.

I finally throw up a solution you like, so you critique my set up of the sample data. There's no pleasing some people. 😀

I've got to go to a wedding, but you know both out solutions are not optimal solutions for doing a search/replace over a great many rows. What's needed is a subquery or inline table valued function version.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

The bit about COLLATEs is new to me. I'll have to test it out. Where did you pick up that bit of information?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Paul White SSC Guru Points: 150467 More actions · Answer 9

The Dixie Flatline (3/20/2010)
The bit about COLLATEs is new to me. I'll have to test it out. Where did you pick up that bit of information?

https://connect.microsoft.com/SQLServer/feedback/details/512459/replace-function-extremely-slow-with-non-binary-windows-collation :w00t:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 10

The Dixie Flatline (3/20/2010)
I finally throw up a solution you like, so you critique my set up of the sample data. There's no pleasing some people. 😀

Nah, it was the same way in the first post.

What's needed is a subquery or inline table valued function version.

Sadly in-line TVFs are out. I have tried. There isn't a way to use the @variable trick in a single SELECT, while also returning data, as is required for an in-line TVF. CLR is the way to go 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi