March 20, 2010 at 11:21 am
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
March 20, 2010 at 11:44 am
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))
March 20, 2010 at 2:19 pm
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;
March 20, 2010 at 3:05 pm
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
March 20, 2010 at 3:15 pm
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!
March 20, 2010 at 3:15 pm
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
March 20, 2010 at 3:18 pm
The Dixie Flatline (3/20/2010)
No recursive CTE this time, Paul? 😉
Recursive CTEs are sooooo yesterday. Everything involves hierarchyid today 😀
March 20, 2010 at 3:30 pm
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
March 20, 2010 at 3:32 pm
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
March 20, 2010 at 3:36 pm
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?
March 20, 2010 at 3:38 pm
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 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply