REPLACE help

[mossbs]

Hi guys,

Need to do an UPDATE and REPLACE on a column in one of my tables.

The data concerned will be along the lines of this...

DE0012.00
DE0013.00
DE0017.00
DE0017.01
DE0017.02
DE0017.03
DE0017.04
DE0017.05

There are 2 things i need to do...

If the data ends in '.00' i need it to remove all of this
- so DE0012.00 would become DE0012

If the data doesn't end with '.00' but say '.001' i need to just remove the '.'
- so DE0017.01 would become DE001701

i know that the code below will remove all of the fullstops... but how can i get it to do both aspect of what i need to do?

update NM_CodesFromNMNew
set dealer_code = REPLACE(dealer_code, '.','')
where dealer_code like 'DE%'

cheers guys.

 

[gmmastros]

Nested replace

update NM_CodesFromNMNew
set dealer_code = REPLACE([!]REPLACE([/!]dealer_code[!], '.00','')[/!], '.','')
where dealer_code like 'DE%'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mossbs]

Fantastic - thanks George.

As i was using it in a SSIS package... i ended up having 2 'Exec SQL Tasks' one replacing '.00' and then one replacing '.'

But may change it as yours does it all in one go and therefore saves connecting to the database a 2nd time etc.

Cheers.