Updattion issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Updattion issue

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-01 : 20:36:55

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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-02-01 : 20:59:28

1) You can use REPLACE to transform the first 'OMP_' to 'KLM_'

update MyTable
set MyCol = Replace(MyCol, 'OMP_', 'KLM_')

2a) You could use REPLACE to transform the subsequent 'OMP_' to '' in a loop (N is unknown).

-- loop while @@RowCount <> 0
begin
update MyTable
set MyCol = Replace(MyCol, 'OMP_', '')
end

2b) Use a static number of REPLACE (N is known)

update MyTable
set MyCol = Replace(Replace(Replace(MyCol, 'OMP_', ''), 'OMP_', ''), 'OMP_', '')

HTH

=================================================================
Egotism is the anesthetic that dulls the pain of stupidity. -Frank William Leahy, football coach (1908-1973)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-01 : 21:28:38

I like solution 2b) but I think it needs to be tweeked slightly:


set nocount on
declare @tb Table (on_description varchar(200))
insert @tb values ('OMP_Seras')
insert @tb values ('OMP_OMP_OMP_OMP_uong')
insert @tb values ('OMP_OMP_Mike')
insert @tb values ('OMP_OMP_OMP_OMP_Angel')


Select	replace(
	 replace(
	  replace(
	   replace(on_description, 'OMP_OMP_OMP_OMP_', 'KLM_'),
	   'OMP_OMP_OMP_', 'KLM_'),
	  'OMP_OMP_', 'KLM_'),
	 'OMP_', 'KLM_') as on_description
From	@tb

--Select Replace(Replace(Replace(on_description, 'OMP_', ''), 'OMP_', ''), 'OMP_', '') from @tb

Be One with the Optimizer
TG

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-01 : 21:37:09

Thanks a lot........

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-02 : 03:13:52

Original reply removed, just re-read the question

Andy

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-02 : 03:32:01

What about

set nocount on
declare @tb Table (on_description varchar(200))
insert @tb values ('OMP_Seras')
insert @tb values ('OMP_OMP_OMP_OMP_uong')
insert @tb values ('OMP_OMP_Mike')
insert @tb values ('OMP_OMP_OMP_OMP_Angel')

UPDATE @tb
SET on_description = 'KLM_' + REPLACE(on_description,'OMP_','')

SELECT *
FROM @tb

Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources