Update part of a character string 1

[cisscott]

I have a SQL database field (SQL Server 2K SP4) that contains event codes, for example "TM0601". I need to run a query to update the field and change the "TM" part to "CM" for all records in the table.. I cannot for the life of me come up with the simple code for this, even though I have done it before =(. Can someone enlighten me? THANKS !

 

[dk87]

So I am at a home PC right now without SQL on it, so test this with a SELECT first...

Update Events
Set EventCode = 'CM' + right(EventCode,len(Eventcode)-2)
where Left(EventCode,2) = 'TM'

 

[donutman]

Here's another way:

[Blue]UPDATE[/Blue] Events
   [Blue]SET[/Blue] EventCode[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]EventCode[Gray],[/Gray][red]'T'[/red][Gray],[/Gray][red]'C'[/red][Gray])[/Gray]

-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]