SQL Replace Pattern

hey, i'm looking replace/update instances of state abbreviation. this:

<span class="region">ca 98657

<span class="region">fl 33025

<span class="region">ny 78346



... etc, etc. there no closing <span> tag, want update these abbreviations be:

<span class="region">ca</span> 98657

<span class="region">fl</span> 33025

<span class="region">ny</span> 78346

... etc, etc. in other words want find instances of:

<span class="region">



... skip/count 2 characters , add:

</span>

 

... how can this?

you can turn select command update way (if using sql 2005 , up)

declare @pattern varchar(30) set @pattern = '%<span class="region">%' ;with cte as (select region,  stuff(region, patindex(@pattern,region) +  len(@pattern),0,'</span>') as corrected from @t  where region like @pattern)  update cte set region = corrected select * from @t 

instead of @t in code use own table name.

---

premature optimization root of evil in programming. (c) donald knuth

naomi nosonovsky, sr. programmer-analyst

my blog

SQL Server  >  Transact-SQL