how to replacing a variable substring 1

[baboudi]

Hi all,

i have a substring in the format:
<name>john</name><lastname>Smith</lastname><left>true</left><datejoined>00-00-00</datejoined><member>true</member>
i am trying to change part of the substring within the
<datejoined>00-00-00</datejoined> to another value from a join with another table

i found out that the replace statement can be used but
my problem is because the dates within the <datejoined> </datejoined> to change varies per user

so whatever the date is in between the datejoined parameter, i have to replace it to qnopther value.
i know how to select the substring

select substring(properties, (charindex('<datejoined>', properties)+12),
(charindex('</datejoined>', properties)-(charindex('<datejoined>', properties)+12))))

but faling on replacing the value

thanks for pointers

babou

 

[bborissov]

DECLARE @Test varchar(200)
SET @Test = '<name>john</name><lastname>Smith</lastname><left>true</left><datejoined>00-00-00</datejoined><member>true</member>'

SELECT LEFT(@test, charindex('<datejoined>', @test)+11)+'12-12-12'+ --- New value
       SUBSTRING(@test, charindex('</datejoined>', @test),8000)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[baboudi]

Thanks.

Ok, I understand what you suggested.

once the sting manipulation is complete, i need to replace it with the original string.

so i will have to do it in 2 phases:
1. modify all the srings into a temp table
2. update the original table with the modified string from the temp table

 

[baboudi]

managed it using the STUFF statement

STUFF ( character_expression, start, length, character_expression )