Search/Replace string 2

[theevilone]

I want to search for a string(&quot within a table and replace it with another string(in). I am using a SQL Server db.

Can someone please help. Thanks in advance.

 

[CFDude]

When you say table, do you mean multiple columns in the table?

You can do a simple update for single field --

Update myTable
Set column = 'StringYouWant'
Where column = 'StringYouAreSearchingOn'

For multiple columns, the only thing I can think of is to first do a select and loop through the fields, check the field and do the update there.

Does this answer your question? If not, post an example of you what you want to accomplish.

 

[theevilone]

Thank for the response, CFDude.

What I am trying to rectify is this :-

In two fields within my db, I have used (&quot instead of (in). e.g. I have used 6" instead of 6in. I want to write a script that can replace (&quot with (in) in those fields. I would appreciate any help.

Ravi

 

[CFDude]

Gotcha -- When I saw the " and the (In) I thought you were referring to the sql quotes and the IN Predicate -- it's friday and early

Which DB are you using? I am sure there are some functions for your DB that could make this quicker, but here is a generic way in CF that you could do it:

Query the database and do a select of the records, loop through that -- check to see if the field(s) have a quote symbol and relace it with in, and do an update.

e.g
<cfquery name=&quot;foo&quot; datasource=&quot;bar&quot;>
Select recordID, field1, field2 from myTable
</cfquery>

<cfloop query=&quot;foo&quot;>
<cfif find('&quot;',field1) neq 0>
<!--- I set the record ID to a temp var, because I have run into problems using a query variable inside another query when it is in a loop, setting it to a temp variable gets around this.
--->
<cfset tmpID = recordID>
<cfset NewField1 = Replace(field1,'&quot;',&quot;in&quot;,&quot;all&quot>
<cfquery name=&quot;foo1&quot; datasource=&quot;bar&quot;>
Update myTable
Set field1 = '#NewField1#'
where field1 = tmpID
</cfquery>
</cfif>
<!--- Now do the same for field2 --->
<cfif find('&quot;',field2) neq 0>
<cfset tmpID = recordID>
<cfset NewField2 = Replace(field2,'&quot;',&quot;in&quot;,&quot;all&quot>
<cfquery name=&quot;foo1&quot; datasource=&quot;bar&quot;>
Update myTable
Set field1 = '#NewField2#'
where field1 = tmpID
</cfquery>
</cfif>



</cfloop>

 

[theevilone]

Thanks for the response CFDude.

I am using a Sql Server DB.

I get the following error message, when I try the above. Can you help.

ODBC Error Code = 22005 (Error in assignment)


[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'x' to a column of data type int.

Thanks in advance.

 

[pcorreia]

The problem is in the update statement. Here's a corrected version:

       <cfquery name=&quot;foo1&quot; datasource=&quot;bar&quot;>
       Update myTable
       Set field1 = '#NewField1#'
       where recordID = tmpID
       </cfquery>

 

[CFDude]

You're right -- I missed that.-- but I think you meant to enter:

<cfquery name=&quot;foo1&quot; datasource=&quot;bar&quot;>
Update myTable
Set field1 = '#NewField1#'
where field1 = #tmpID#
</cfquery>

 

[pcorreia]

Yup...

Obviously, we're sharing a brain today. P-)

 

[theevilone]

Thanks pcorreia and CFDude. It works.