Updation issue 1

[SqlHunter]

Here I have a column called on_description and I want to update this column to like where there is OMP_ to KLM_
and even if there is multiple instances the result should be KLM_

on_description
---------------
OMP_Seras
OMP_OMP_OMP_OMP_uong
OMP_OMP_Mike
OMP_OMP_OMP_OMP_Angel

result should be
------------------


KLM_Seras
KLM_uong
KLM_Mike
KLM_Angel

 

[r937]

select replace(replace(replace(replace(
        on_description,'OMP_OMP_','OMP_')
                      ,'OMP_OMP_','OMP_')
                      ,'OMP_OMP_','OMP_')
                      ,'OMP_','KLM_')
  from yourtable

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[cLFlaVA]

r-

don't you mean update?

*cLFlaVA
----------------------------
[tt]tastes great, less filling.[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[r937]

oh

yeah

thanks

update yourtable
   set on_description
     = replace(replace(replace(replace(
        on_description,'OMP_OMP_','OMP_')
                      ,'OMP_OMP_','OMP_')
                      ,'OMP_OMP_','OMP_')
                      ,'OMP_','KLM_')

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[SqlHunter]

Thanks

 

[donutman]

If you've already done the update, it wouldn't hurt to double check if there is an instance of 'KLM_KLM_' because of the original instance of 5 OMP_s.
-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]

 

[r937]

oh ye of little faith

test it, karl

even though there was no instance of 5 OMG's in the original data, my query handles even 6 of them

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[donutman]

Good point! But since there is a limit (8 I think), it still wouldn't hurt to check. I reasoned that the OMP_s got there through a systematic process, so the number of them is probably unknown unless SqlHunter actually checked.
-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]