>>>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.
>>>
>>>Ideas?
>>
>>Is this what you want?
>>Function txt2html
>> Lparameter tcHTML
>>
>> tcHtml = Strtran(tcHtml, [<], [<])
>> tcHtml = Strtran(tcHtml, [>], [>])
>> tcHtml = Strtran(tcHtml, ["], ["])
>> tcHtml = Strtran(tcHtml, [&], [&])
>> tcHtml = Strtran(tcHtml, [ ], [ ])
>>
>>Return tcHtml
>
>
>No. I am already doing that in vfp before storing to the 2nd column when the 1st column is updated. 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.
>
>
>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?
Unfortunately I can't help you, hopefully someone else can.
