>>>>>There are times when I need to copy data from a text column in sqlserver to another text column. The destination text column must have all xml reserved characters transformed. For example if the originating column has "call in < 10 minutes" then I have to transform it to "call in < 10 minutes"
>>>>>
>>>>>I use a sql statement with
>>>>>
>>>>>"UPDATE " + TableName + " SET " + column2 + " = (select " + column1 + " from " + TableName + " WHERE column1.id = ' " + idvalue + " ' )....
>>>>>
>>>>>to handle copying the 1st column's data to the destination column which works great, however I now need to transform those reserved characters if they exist because the destination column has the same information but formatted for xml and do not want to add any additional process to store into a variable and transform then copy if I can avoid it.
>>>>>
>>>>>Whenever the 1st column is updated, I transform those characters before also updating the 2nd column. The situation I have to account for is the initial copying of the data from column1 to column2. That is done via a single sqlserver update statement and I need to transform those characters inside the sql statement before inserting them into the 2nd column. This is done "on the fly" periodically. There are times when new records are created that have the data only inserted into column1. Column1 can have reserved characters. We don't insert into column2 which cannot have reserved characters until later. It is that process that copies from column1 to column2 that I need to handle it now. Basically we have a record that has plain text in column1 for older vfp apps and xml ready text (for RTF display) in column2 for newer apps.
>>>>>
>>>>>So column2 is the same data as column1 except it has those reserved characters transformed. If an older app adds a record and inserts data in to column1, when the newer app reads or uses that record we copy column1 to column2 (which is where I need to transform those characters) and then from that point on we update both columns putting plain text into column1 and rtf formatted text into column2 so the data is the same in both columns. When that process is occurring, it is a single sql statement that selects from column1 and updates column2. It is that single sql statement where I need to transform those characters from the select statement. I thought of FOR XML but I don't want xml tags, I just want any reserved characters converted appropriately.
>>>>>
>>>>>

>>>>>UPDATE table SET column2 = (SELECT column1 from table....    -- in this select statement I need to transform those characters if possible
>>>>>

>>>>>
>>>>>Can it be done without storing column1 to a variable first?
>>>>
>>>>
>>>>Tracy,
>>>>
>>>>Can this help you ? http://technet.microsoft.com/en-us/library/ms186862.aspx
>>>>
>>>>You'll need nested replace()
>>>
>>>I think not, because we are working with TEXT fields... :( but I will verify. I tried replace and ran into that issue....
>>>
>>>SORRY ALL - I should have mentioned it was a TEXT column.
>>
>>UPDATE - select reply to see the correct syntax
>>
>>This works
>>
>>

>>declare @Tmp table
>>(	Col1	Text,
>>	col2	Text
>>)
>>
>>insert into @Tmp (Col1) values ('<aa>')
>>
>>
>>select cast(Replace(Replace(Replace(cast( col1 as nvarchar(max)), '&', '&'), '<', '<'), '>', '>') as Text)
>>	from @Tmp
>>
>>

>
>I have to make this work with SQL2000 as well it looks like (for older systems) which throws another wrench into it.... :( but hopefully....



Shouldn't be a problem if we can trust the BOL

http://technet.microsoft.com/en-us/library/aa226054(v=SQL.80).aspx
http://technet.microsoft.com/en-us/library/aa238479(v=SQL.80).aspx
http://technet.microsoft.com/en-us/library/aa258271(v=SQL.80).aspx