My appologies. I posted the wrong archive stuff. The correct query is

posted below. As you can see, 6 replacements is enough.

set nocount on

create table #t(id int,col1 varchar(4000))

begin

end

update #t

set col1 =

Replace(

Replace(

Replace(

Replace(

Replace(

Replace(col1, replicate('X',132),'X')

, replicate('X', 21),'X')

, replicate('X', 6),'X')

, replicate('X', 3),'X')

, replicate('X', 2),'X')

, replicate('X', 2),'X')

select * from #t where col1<>'aXa'

drop table #t

Gert-Jan

> Alas! It does not work for more than 4000 characters as you say. The limit is

> 196 characters.

> The general solution is this:

> x{n} - 1 = maximum characters allowed with n REPLACE statements

> x{0} = 2 and Phi{n} is the prime number function starting with Phi{1} = 2

> x{n + 1} = (x{n} - (Phi{n} - 1)) * Phi{n} + (Phi{n} -1)

> doing out the math:

> x{1} = (2 - (2 - 1)) * 2 + (2 - 1) = 3

> x{2} = (3 - (3 - 1)) * 3 + (3 - 1) = 5

> x{3} = (5 - (5 - 1)) * 5 + (5 - 1) = 9

> x{4} = (9 - (7 - 1)) * 7 + (7 - 1) = 27

> x{5} = (27 - (11 - 1)) * 11 + (11 - 1) = 197 -- this is the 5 replace limit + 1

> ... but from here it increases more rapidly...

> x{6} = (197- (13 - 1)) * 13 + (13 - 1) = 2417

> x{7} = (2417- (17 - 1)) * 17 + (17 - 1) = 40833

> So a minimum of seven REPLACEs are needed in a single statement to always cover

> the varchar data type in MS SQL.

> Bob

> > Bob,

> > the following statement will do what you want for a varchar column of

> > over 4000 characters, and will do it in one run.

> > update MyTable

> > set MyVarcharColumn =

> > Replace(

> > Replace(

> > Replace(

> > Replace(

> > Replace(MyVarCharColumn, 'XXXXXXXXXXX','X')

> > , 'XXXXXXX','X')

> > , 'XXXXX','X')

> > , 'XXX','X')

> > , 'XX','X')

> > Gert-Jan

> > > What is the best way to replace all occurrences of more than one character

> > > together with just one character?

> > > I'm interested in collapsing whitespace within strings, but I'll use 'X' to

> be

> > > clear in this example:

> > > 'aaaaXaaaXXXaaaXXaa' would collapse to 'aaaaXaaaXaaaXaa'

> > > I know it can be done with iterative calls to REPLACE, but I'm hoping to

> find a

> > > non-procedural solution.

> > > TIA,

> > > Bob