Hi,
I need to update just a portion of a column value in a table.
example,
Order_data = 'source=ABD00050&ordsrc=&ecode=ABD00001'
Order_data ='source=ABD00050&ecode=ABD00001'
for both of these values of Order_data I just need to update value of ecode..
Please help..
Thanks,
Reva
Replaces all occurrences of the second given string expression in the first string expression with a third expression.
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )
'string_expression1'
Is the string expression to be searched. string_expression1 can be of character or binary data.
'string_expression2'
Is the string expression to try to find. string_expression2 can be of character or binary data.
'string_expression3'
Is the replacement string expression string_expression3 can be of character or binary data.
Returns character data if string_expression (1, 2, or 3) is one of the supported character data types. Returns binary data if string_expression (1, 2, or 3) is one of the supported binary data types.
I have tried using replace but am not sure how to use with an UPDATE statement because I need to update values in a table.
Thanks,
declare @text1 varchar(200),@text2 varchar(200)
select @text1= 'source=ABD00050&ordsrc=&ecode=ABD00001'
select @text2 ='source=ABD00050&ecode=ABD00001'
select replace(@text1,'ABD00001','ABD00002')
select replace(@text2,'ABD00001','ABD00002')
Reva wrote:
I have tried using replace but am not sure how to use with an UPDATE statement because I need to update values in a table.
Thanks,
use northwind
select * from employees
update employees set lastname=replace(lastname,'ling','lang')
select * from employees
update employees set lastname=replace(lastname,'lang','ling')
select * from employees
I was able to do it for the two records but now the problem is that I need to do this update for 150 records where the value of the source is diff and acc the value of ecode is to be set.
like if source is ABD00051 the ecode is to be set to ABD00051 (nearly 25 records)
if source is ABD00043 the ecode is to be set to ABD00043 (nearly 10 records)
if source is ABD00045 the ecode is to be set to ABD00045 (almost 5 records) and so on ..
Now I was thinking of a cursor but then again teh value is changing.so I need to put if statements..Does Case work somehow in this situation because I was trying
UPDATE temp
SET
CASE order_data
WHEN like 'source=ABD00051%'
THEN order_data = (replace(order_data,'ABD00001','ABD00051'))
WHEN like 'source=ABD00043%'
THEN order_Data=(replace(order_data,'ABD00001','ABD00051'))
END
but this is giving an error .."Incorrect syntax near the keyword 'CASE'."
Please suggest.
Thanks,
this can be done in a very straightforward manner
use northwind
begin transaction
select * from employees
update employees set lastname=replace(lastname,'ling','lang') condition 1
update employees set lastname=replace(lastname,'cock','hen') condition 2 --thats 'c o c k'
update employees set lastname=replace(lastname,'worth','less') condition 3
select * from employees
rollback transaction
Thanks,
I did that using a cursor and just one update statement.
Declare a cursor
WHILE(@@FETCH_STATUS = 0)
Begin
SELECT @source = substring(@order_data,8,8)
UPDATE #temp
SET
Order_Data = (Replace(Order_data,'ABD00001',@source))
WHERE
<conditions>
END
But thanks for your efforts.