Skip to main content

Create a new column from another with replace function

Hi,
 
I would like to create a new column out of another one. I have one column which contains text. For example an address. Now I want to replace certain parts within this string. For example I want to remove "AND". So replace "AND" with "".
 
 
So the field address contains "Road 1 and Plaza" and the new created field temp1 should contain "Road 1 Plaza".
 
I use the replace function mentioned here:
https://ideascripting.com/node/71
 
My full code is as follows:
<code>
Sub Main    Call AppendField()End SubFunction AppendField    Set db = Client.OpenDatabase("myfile.IMD")    Set task = db.TableManagement    Set field = db.TableDef.NewField    field.Name ="temp1"    field.Description =""    fied.Type =WI_CHAR_FIELD    field.Equation = Replace(ADDRESS,"AND","")    field.Length=48    task.AppendField field    task.PerformTask    Set task = Nothing    Set db = Nothing    Set field = NothingEnd FunctionFunction Replace(temp_string As String, temp_find As String, temp_replace As String)    Dim str_len, i As Integer    Dim new_string As String    Dim temp_char As String    Dim temp_one_char As String    Dim temp_no_chars As Integer       new_string = "" 'set the new string    str_len = Len(temp_string) 'length of the string to search in    temp_no_chars = Len(temp_find) 'number of characters to search for       For i = 1 To str_len        temp_char = Mid(temp_string,i, temp_no_chars) 'get a partial string to compare        Temp_one_char = Mid(temp_string,i, 1) 'get the character it is based on        If temp_char = temp_find  Then 'validate if the partial string is what we are lookign for            'if it is, replace it and skip i to take into account the no of characters            temp_char = temp_replace            new_string = new_string & temp_replace            i = i + (temp_no_chars - 1)        Else            new_string = new_string & Temp_one_char        End If           Next i       replace = new_stringEnd Function
</code>
 
However, this throws an error. The crucial point is that field.Equation = Replace(ADDRESS,"AND","") is not working. How can I fix this?
 
 EDIT: My apologies, I do not know how to correclty paste the code here, so that it gets displayed correctly, therefore I attached a txt with the code.
 
Thanks for any help!

Brian Element Sat, 10/16/2021 - 05:48

HI Bert_B, 

The problem is your field equation has to be a proper equation editor equation.  So you can't call functions for your field equation.  Since you want to use the replace function then you could use the equation editor @Replace function.  

In your scenario the line should be:

field.Equation = "@Replace(ADDRESS, ""AND"", """")"

or for better readability you can do this:

field.Equation = "@Replace(ADDRESS, " & chr(34) & "AND" & chr(34) & ", " & chr(34) & chr(34) & ")"