Substring Search & Replace

[Fredriksson via AccessMonster.com]

I only want to replace a certain character within a string. Will the Replace
command do a substring search and replace? What happens if some of the rows
contain the character that I am trying to replace and other row do not? I am
trying to strip unprintable characters in the column.

Is this the correct SQL syntax?

UPDATE Table1
SET REPLACE( [ColumnName], Chr(9), "")

 

[Jerry Whittle]

Your SQL statement is missing something. You need to say what field is to be
updated.

UPDATE [Table1]
SET [Table1].[ColumnName] = Replace([ColumnName],Chr(9),"");

If Replace can't find the Chr(9) within the field for a record, it won't do
anything although it will say that it is updating all the rows in the table.
If you want to see how many rows are actually changed, run something like
this:

UPDATE [Table1]
SET [Table1].[ColumnName] = Replace([ColumnName],Chr(9),"")
WHERE Instr([ColumnName],Chr(9)) > 0;

Actually this would be best as Replace will bomb out if it hits an null
fields.

 

[Allen Browne]

Something like this might do the job:

UPDATE Table1
SET [ColumnName] = REPLACE( [ColumnName], Chr(9), "")
WHERE [ColumnName] Like "*" & Chr(9) & "*";