Replace Statement, How to remove unwanted characters

[daguas]

Using SQL2005
I have an application that was adding zeros to a nvarchar field, we have corrected the applications behavior, but I need to delete these zeros in existing records. I can select the records to update using the following
Select *
From dbo.Contracts
Where dbo.ContractNumber Like ('%O002')

I need to remove the 00 from between the letter O and the number 2 "O002"

I know its a replace statment, but need help with the syntax.

THanks

 

[markros]

In your case you would need to look at STUFF() function.

 

[markros]

In case of always 2 characters, you can try

set @ddd = 'ABDCO002RDDB00'

select STUFF(@ddd, CHARINDEX('O',@ddd,1)+1,2,'')

 

[PDreyer]

update dbo.Contracts
set ContractNumber=replace(ContractNumber,'O002','O2')
Where ContractNumber Like ('%O002')