SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Search a value and replace with new character in columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/11/2001 :  22:40:02  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Vishnu writes "I have a table named MYTABLE containing FIELD1 which has 3 sample records.

Vi$$$u
Sam'le
Test
I want to execute a stored procedure that will accept two parameters. First parameter to search and second parameter to replace.

If i execute the stored procedure as follows,

exec Myprocedure "$'" "Kp" then my expected result should be like
ViKKKu
Sample
Test
If i use the following code

create procedure proc1(@find varchar(6),@replace varchar(6))
as
declare @slen int,@currpos int
declare @repchar char(1)
select @currpos=1
select @slen= datalength(@find)
while @currpos <= @slen
begin
select @repchar = substring(@replace,@currpos,1)
update mytable set field1 = isnull(stuff(field1,charindex(substring(@find,@currpos,1),field1),1,substring(@replace,@currpos,1)),field1)
select @currpos=@currpos + 1
end

am getting the following result.

ViK$$u
Sample
Test

Can you help me to solve this????

Thanks"

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 07/11/2001 :  22:40:02  Show Profile  Visit graz's Homepage  Reply with Quote
Is there a reason you're not using the REPLACE function?
Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/11/2001 :  22:58:37  Show Profile  Reply with Quote
Yeah, 'cause he is specifying two values for each argument of replace.

But graz is right. You should be using replace, it will make your life a whole lot easier. If you insist on using a single sp to find and replace multiple values look at passing in a csv into a temp table and then pull each row as the arguments for the replace function.


M2C
Justin

Go to Top of Page

vishroopa
Starting Member

India
1 Posts

Posted - 07/12/2001 :  01:00:02  Show Profile  Reply with Quote
Hi This is Vishnu again,
Thanks for your replies. But i had found out the solution.

CREATE PROCEDURE PROC1 (@FIND VARCHAR(6), @REPLACE VARCHAR(6))
AS
DECLARE @SLEN INT, @CURRPOS INT, @FLDIDX INT
DECLARE @REPCHAR CHAR(1)
DECLARE @CURSOR_FLD VARCHAR(255)
SELECT @CURRPOS = 1
SELECT @SLEN = DATALENGTH(@FIND)

DECLARE MYCURSOR CURSOR
FOR SELECT FIELD1 FROM MYTABLE

WHILE( @CURRPOS <= @SLEN)
BEGIN
OPEN MYCURSOR
FETCH MYCURSOR INTO @CURSOR_FLD

SELECT @REPCHAR = SUBSTRING(@REPLACE,@CURRPOS,1)
WHILE(@@SQLSTATUS = 0)
BEGIN
SELECT @FLDIDX = CHARINDEX(SUBSTRING(@FIND,@CURRPOS,1),@CURSOR_FLD)
WHILE (@FLDIDX != 0)
BEGIN
SELECT @CURSOR_FLD = ISNULL(STUFF(@CURSOR_FLD,@FLDIDX,1,@REPCHAR),@CURSOR_FLD)
SELECT @FLDIDX = CHARINDEX(SUBSTRING(@FIND,@CURRPOS,1),@CURSOR_FLD)
END
UPDATE MYTABLE SET FIELD1 = @CURSOR_FLD WHERE CURRENT OF MYCURSOR
FETCH MYCURSOR INTO @CURSOR_FLD
END
SELECT @CURRPOS = @CURRPOS + 1
CLOSE MYCURSOR
END

If you come up with optimized code, it would be great.

Thanks

Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 07/12/2001 :  09:51:44  Show Profile  Visit robvolk's Homepage  Reply with Quote
CREATE PROCEDURE PROC1 (@FIND VARCHAR(6), @REPLACE VARCHAR(6)) AS
DECLARE @findchar Char(1), @repchar char(1), @curpos int, @length int
SELECT @curpos=1, @length=DataLength(@find)

WHILE @curpos<=@length
BEGIN
SELECT @findchar=SubString(@find, @curpos, 1), @repchar=SubString(@replace, @curpos, 1)
UPDATE MyTable SET Field1=Replace(Field1, @findchar, @repchar)
SELECT @curpos=@curpos+1
END


Edited by - robvolk on 07/12/2001 09:54:35
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000