Remove spaces in middle of string

[dazzer123]

Hi,

I have a database of about 12,000,000 rows and on one particular field I have a need to remove all spaces from the left, right and middle of the string. I've used LTrim and RTrim to complete the first bit however i'm having trouble removing the spaces from the middle of the string, I can see how to do it in VB by looping through each record removing the space and updating the DB but with 12,000,000 rows this is going to take too long.

Is there anyway I can do this in a stored procedure or in DTS (I am a complete novice with DTS by the way!)

Thanks all in advance for you help

Dazzer

 

[dazzer123]

It's ok, I got it ;-)

=Replace([Fieldname], ' ', '')

 

[donutman]

It may timeout on you anyway. If it does, I'd use a where clause with a range on the PK in order to breakup the 12,000,000. Of course you could change the timeout value, but there's nothing more annoying than not making the value big enough!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[maswien]

How this work when there are more than one space in the middle?

try this:


alter table myTable add intCol int

while @@rowcount > 0
begin
update myTable set intCol = len(strCol)
update myTable set strCol = replace(strCol, ' ', '')
where intCol > len(replace(strCol, ' ',''))
end

 

[maswien]

sorry it's wrong post, I realize that replace() will do it anyway