Cannot replace this?

[NewbiDoobie]

I am trying to do something I am not sure can even be done.

I have code which may contain several abbreviations and I need to update it with the correct description from an abbreviations table.

This is the type: W/ oop
W/o oop

Abbreviations table contains these abbreviations:
Abbr Def
W/ With
W/O Without
oop optional oil pan


I need to update the W/ oop = With optional oil pan and the W/o oop = Without optional oil pan.

Here is the code I am trying but I am getting:
Incorrect syntax near 'replace'.

declare @rec int,
	@type varchar(30),
	@Abbr varchar (2),
	@Def varchar(60)

declare Repl cursor for select recno, type from dbo.X_OILCAP where type is not null and recno = 90

open Repl

fetch next from Repl into @rec, @type

while @@fetch_status = o

begin

	declare replacer cursor for select ABREVIATIO,DEFINITION from AccessSpecs.dbo.ABBR

	open relacer

	fetch next from replacer into @abbr, @def

	while @@fetch_status = 0
	begin

	replace(@type, @Abbr, @def)

	print @type


	fetch next from replacer into @abbr, @def
	end
	deallocate replacer


fetch next from Repl into @rec, @type


end
deallocate Repl

 

[Jamfool]

with regards to your cursor. you will need to change it to

select @type = replace(@type, @Abbr, @def)

...you will obviously need to create the update.


However you might want to think about writing simple updates rather than a cursor as it may cause replacement problems

replacing all w/ first would mess up the without abb ones.

(need to be aware of this when create your replace)

update dbo.X_OILCAP set type = replace (type,'W/O','Without')
from dbo.X_OILCAP where type is not null and recno = 90

update dbo.X_OILCAP set type = replace (type,'W/','With')
from dbo.X_OILCAP where type is not null and recno = 90

update dbo.X_OILCAP set type = replace (type,'oop','optional oil pan')
from dbo.X_OILCAP where type is not null and recno = 90

 

[vongrunt]

To partially eliminate such problems... use ORDER BY LEN(ABBR) DESC in cursor's query.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NewbiDoobie]

I understand. After reading explanations, this would be a bad idea with over 600 abreviations and 3000 type which could contain up to 4 abbreviations.

Maybe i can find a way to parse the tpe and reun the abbreviations on that

 

[gmmastros]

@Abbr is declared VarChar([red]2[/red])

2 of your abbreviations have 3 characters [red]W/o[/red] and [red]oop[/red], so I would expect that to be a problem.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[NewbiDoobie]

Figured it out I am going to do a loop to get this, but instead of a replace, i am going to use

DECLARE @mystring varchar(255), @myword varchar(50), @mysentence varchar(255), @mynewword varchar(60)
DECLARE @i int,@j int
set @mysentence = ''
SELECT @mystring = 'w/ oop w/o oop'
select @mystring = @mystring + ' ' 
SELECT @i = 0,@j = 0
IF SUBSTRING (@mystring, LEN (@mystring), 1) <> ' '
BEGIN
   SELECT @mystring = @mystring + ','
END
SELECT @i = CHARINDEX (' ', @mystring, @i + 1)
WHILE @i > 0
BEGIN
   SELECT @myword = SUBSTRING (@mystring, @j+1, (@i - @j) -1)
   SELECT @myword
   SELECT @j = @i
   SELECT @i = CHARINDEX (' ' , @mystring, @i + 1)	
-----------------------------------------------------
Select @mynewword = DEFINITION from AccessSpecs.dbo.ABBR where ABREVIATIO = @myword
-----------------------------------------------------
set @mysentence = @mysentence + ' ' + @mynewword  
END
select  @mysentence
update dbo.X_OILCAP set type = @mysentence where recno = @rec

 

[is47501]

I came up something that may work well for you.

As I understand it you need to update the data in the table, so I designed this with that in mind. I also did this in a hurry and on a limited set of data because I need to leave for the weekend. Please check my work carefully.


WHILE EXISTS
(SELECT * from X_OILCAP X INNER JOIN ABBREVIATIONS A ON CHARINDEX ( A.abbr , X.TYPE ) > 0 AND REC_NO = 90) BEGIN


UPDATE X_OILCAP SET TYPE = REPLACE(X_OILCAP.TYPE,rplcr.abbr, ' ' + RTRIM(rplcr.def) + ' ') FROM X_OILCAP INNER JOIN
(SELECT TOP 1 A.abbr, A.def from X_OILCAP X INNER JOIN ABBREVIATIONS A ON CHARINDEX ( A.abbr , X.TYPE ) > 0 AND REC_NO = 90) rplcr
ON CHARINDEX ( rplcr.abbr , X_OILCAP.TYPE ) > 0 AND REC_NO = 90


END

SELECT * from X_OILCAP

It can of course be modified to return just a recordset instead of updating the table.


Best of luck and I hope this helps.

 

[k108]

Perhaps I am misunderstanding, but it seems like what you want to do is extremely simple and does not require the use of a cursor:

Update mytable set mycol = "new value"
where mycol = "old value"

Of course maybe I am not understanding what you are trying to do.