Re: Replacing strings as part of a query
What database are you using? This sounds like something that would be best handled at the database level, instead of running it through CF. - Original Message - From: Richard Crawford Actually, my question about replacing special characters (thanks to all who answered, by the way) was part of a larger question.What I really need to do is select all rows of one table, modify the value of one field, and insert all of the rows, with that one modified field, into another table. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Replacing strings as part of a query
Deanna Schneider wrote: What database are you using? This sounds like something that would be best handled at the database level, instead of running it through CF. Deanna, I'm using SQL Server 7.The problem is that the field with the characters to be replaced is a TEXT field, and you can't use the REPLACE function on a TEXT field.And, unfortunately, the data is too big in some of the records to CAST to VARCHAR. Here is the SQL that I've tried using: - select msgID, msgSent, msgFromType, msgFromID, msgSubject, REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), 'BR') AS newMessage, msgOriginal, attID into tblMessageNew from tblMessage -- In records where the value of msgMessage is greater than 8000 characters, the SQL bombs and no results are returned. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Replacing strings as part of a query
Don't know SQL Server. Sorry. But, as for your 500 error - make sure that show friendly http errors is turned off in your IE preferences. Then, you should at least see more info. - Original Message - From: Richard Crawford Deanna Schneider wrote: What database are you using? This sounds like something that would be best handled at the database level, instead of running it through CF. Deanna, I'm using SQL Server 7.The problem is that the field with the characters to be replaced is a TEXT field, and you can't use the REPLACE function on a TEXT field.And, unfortunately, the data is too big in some of the records to CAST to VARCHAR. Here is the SQL that I've tried using: - select msgID, msgSent, msgFromType, msgFromID, msgSubject, REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), 'BR') AS newMessage, msgOriginal, attID into tblMessageNew from tblMessage -- In records where the value of msgMessage is greater than 8000 characters, the SQL bombs and no results are returned. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Replacing strings as part of a query
Deanna Schneider wrote: Don't know SQL Server. Sorry. But, as for your 500 error - make sure that show friendly http errors is turned off in your IE preferences. Then, you should at least see more info. That in itself isn't an issue (I don't use IE if I can possibly avoid it).I had hoped that I could find more information by checking the CF server log, but it was unrevealing. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Replacing strings as part of a query
Actually, my question about replacing special characters (thanks to all who answered, by the way) was part of a larger question.What I really need to do is select all rows of one table, modify the value of one field, and insert all of the rows, with that one modified field, into another table. Here is the code I've written as an attempt: cfset sourceTable=tblMessage cfset targetTable=tblMessageNew cfquery name=get datasource=DLCampus select * from tblMessage /cfquery pbAll rows selected.Processing.../b/p cfquery name=put datasource=DLCampus cfoutput query=get cfset newMessage = replace(#get.msgMessage#,chr(13)chr(10),BR) insert into tblMessageNew (msgID, msgSent, msgFromType, msgFromID, msgSubject, msgMessage, msgOriginal, attID) values #get.msgID#, '#get.msgSent#', #get.msgFromType#, #get.msgFromID#, '#get.msgSubject#', '#get.msgMessage#', #get.msgOriginal#, #get.attID# /cfoutput /cfquery Unfortunately, it doesn't seem to be quite working.No errors are returned; when I try to run the code, I get a 500 Null error, which is astonishingly uninformative.;-) -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
