Ben, are you saying that the Replace T-SQL function that Teddy mentioned
won't work for the problem Michael raised (since he specifically said he
needed to update a Text column datatype? That would certainly put the skids
on the approach. :-) I've not faced the problem at all myself so was just
helping with the question of where to try such an approach if it would work.
I've done some googling, and I see indeed that there are many pages
explaining that as you say, Replace does not work with Text (or Ntext)
fields (and lamenting that datatype as having various other limitations).
Some also try to offer a solution (albeit complex) to the problem of using
Replace with Text fields, such as:
http://www.sqlteam.com/item.asp?ItemID=15528
and there's a forum discussion of it:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31893
But I decided to dig a little further to find if perhaps this has changed in
SQL Server 2005. What I did find, though, was a discussion of the text (and
ntext) datatypes in particular and saying that they are due to be deprecated
(sounds like it makes sense!). I mention this because it offers the
alternatives to be considered (in your case, varchar(max), with "max" being
a new keyword in SQL 2005):
http://msdn2.microsoft.com/en-gb/library/ms187993.aspx
This discussion (http://msdn2.microsoft.com/en-gb/library/ms178158.aspx)
more specifically indicates that Text should be replaced with varchar(max),
and NText with nvarchar(max). Perhaps that may help.
I realize that many may not be free to change their database column types,
but then some may be, especially if it solves a problem.
/Charlie
http://www.carehart.org/blog/ _____ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ben Johnson Sent: Saturday, December 09, 2006 7:50 AM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] Data Update Just remember that REPLACE won't work on text fields. Varchar, yes. On 12/8/06, Charlie Arehart <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > wrote: To clarify, as is clear from the example, Teddy has shown there the T-SQL way of doing the replace (he doesn't mean the CFML Replace function), which would mean either doing it in the SQL Server Enterprise Manager (or Management Studio in 2005), or trying to do it in a CFQUERY, though often the database driver will preclude use of statements other than SELECT, INSERT, UPDATE, DELETE, and similar. Someone else may suggest to Michael that he could do it all in CFML, pulling the data in via a SELECT, doing the replace in CFML, and then updating the record, but unless you have no other choice, it would be generally more efficient to do it in the database directly. And if you needed to do it recurrently and programmatically (rather than manually), it would still be better to look into creating a stored procedure on the server and calling that. Hope that helps. /Charlie http://www.carehart.org/blog/ _____ From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ] On Behalf Of Teddy Payne Sent: Friday, December 08, 2006 4:44 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] Data Update REPLACE Replaces all occurrences of the second given string expression in the first string expression with a third expression. Syntax REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' ) <snip> Examples This example replaces the string cde in abcdefghi with xxx. SELECT REPLACE('abcdefghicde','cde','xxx') GO ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink <http://www.fusionlink.com> ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink <http://www.fusionlink.com> ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
<<attachment: image002.jpg>>
<<attachment: image002.jpg>>
