How to replace all occurrences of matching string in a database table using ColdFusion

Wor开发者_StackOverflow社区king with a MS Access database, using one particular table, and scattered throughout the table at varying positions in date columns (which themselves can be in varying orders as a result of the data import) is the text "Not known". I want to replace occurrences of that text string across the whole data table.

The only way I can think of doing it is export to a csv format, and do a REReplace then import the data again, but I would like to know if there is a 'slicker' way?

The columns contain data which is a data import from a csv file so all the columns are text, they can contain a mix of "date string", text, numbers (as string) and null.

You can use replace, it follows basic TSQL implementation :

http://msdn.microsoft.com/en-us/library/ms186862.aspx

Here is an example I did updating the customers table of the Northwind sample database:

update customers set Customers.[Job Title] = replace( Customers.[Job Title], 'Purchasing', 'Manufacturing');

So to distill it into a generic example :

update TABLENAME set FIELD = 
    replace( FIELD, 'STRING_TO_REPLACE', 'STRING_TO_REPLACE_WITH' )

That updates the entire table in one statement. Be careful ;)

You can do this using Access, running edit-replace command. If you need to do this in code - you can open recordset, loop through records and for each field run:

rst.fields(i)=replace(rst.fields(i),"Not known","Something")

this is how it works in VBA, beleive you can do something similar in coldfusion

Why not just open the CSV file in Notepad++ (or similar) and do a Find/Replace?