Changing text characters.

[Database Administrator]

I have a text field that I import data into. The data comes into FMPv9 with
a "/" as the 1st and 14th character. I need to change this character to a
":" in the 1st and 14th character without changing anything in between.

I have a script that is moving some data around in the db and would like to
incorporate this process into this script as well so that all of the records
in the db are changed.

I can't grasp how to do this. I looked at replace and substitute, but can't
get it to work.

Any suggestions would be greatly appreciated.

Thanks.

[Database Administrator]

PghOPS wrote:

> I have a text field that I import data into. The data comes into FMPv9 with
> a "/" as the 1st and 14th character. I need to change this character to a
> ":" in the 1st and 14th character without changing anything in between.
>
> I have a script that is moving some data around in the db and would like to
> incorporate this process into this script as well so that all of the records
> in the db are changed.

Use the replace or the set field step in order to replace the field's
contents by a calculation with text result.

The calculation starts with

":" &

Use a text function to put characters 2-13

& ":" & etc.


--
http://clk.ch

[Database Administrator]

PghOPS wrote:
> I have a text field that I import data into. The data comes into FMPv9 with
> a "/" as the 1st and 14th character. I need to change this character to a
> ":" in the 1st and 14th character without changing anything in between.
>
> I have a script that is moving some data around in the db and would like to
> incorporate this process into this script as well so that all of the records
> in the db are changed.
>
> I can't grasp how to do this. I looked at replace and substitute, but can't
> get it to work.
>
> Any suggestions would be greatly appreciated.

You should be able to either:
Set Field[ FieldName; substitute( FieldName; "//", ":") ]
(the extra slash escapes the slash criterion)

or

Set Field[ FieldName; replace( replace( FieldName; 1; 1; ":"); 14; 1; ":") ]