I'm writing a script that substitutes certain words in a field for another word.

i.e Substitute(Field1,"Yellow","Red")

This is fine if the word is Yellow but doesn't work if it is yellow or YELLOW or any other combination of capitals and small letters.

Is there a way to catch all combinations or do I need to do a SUBSTITUTE command for every combination?

Regards

Nick

The Substitute function is case sensitive.

However you can get a comparable result if you use the Replace function with the Position and Length functions to set its parameters. Position is not case sensitive.

To replace multiple occurences with this method, you will have to put the Replace within a loop in a script, and set it to exit the loop when the search string is no longer found, because 'Replace' is a one-shot thing.

Substitute(Upper(field), Upper("Yellow"), "Red")

Dj

I had a problem like this, so was interested to see this post.

Unless Nick wants eveything in his field except the replacement text converted to upper case, then that last suggestion isn't going to help him much.

I tried the suggestion by RobinG here, though, and it seems to be working!

No, this does not convert everything to uppercase!

Replace -- as has been stated numerous times -- will break in multi-user mode due to record locking, and there are no easy ways around it.

Substitute uses a logical expression to decide what gets processed... DJ's suggestion says "pretend everything is uppercase when you look at the strings" not "convert everything to uppercase."

The expression "Substitute(Lower(field), Lower("Yellow"), "Red")" will work equally as well. So would Proper() for that matter.

To bad that it's not true (I taught that the conversion would not occur, but...)

Dj

I have tried it, have you???

If a field called Phrase Field has in it the text string "jack and jill", and you run a script with:

Set Field ["Phrase Field", "Substitute(Upper(Phrase Field), Upper("jack"), "Charles")"]

The field will end up with the text string "Charles AND JILL" in it.

Vaughan, there is NO problem with the Replace *function* in multi-user mode. You are confusing it with the Replace Contents COMMAND, which will have problems with record locking. The replace *function* has no such problems (except in exactly the same ways that the Substitute *function* does).

Instead try:

Set Field ["Phrase Field", "Replace(Phrase Field, Position(Phrase Field, "jack", 1, 1), Length("jack"), "Charles")"]

You will get "Charles and jill" returned. I know. I tried it when I read RobinG's post!!!

Substitute(Substitute(Substitute(field, Lower("Yellow"), Proper("Yellow")), Proper("Yellow"), Upper("Yellow")), Upper("Yellow"), "Red")

the most with min suffer

Dj

But dj, it is still case sensitive, because it doesn't deal with YELlow or YeLLow or YelloW or any of the others, whereas

Replace(field, Position(field, "yellow", 1, 1), Length("yellow"), "red")

is a lot shorter and deals with all 'case' cases.

THaT's What I WaS SaYinG:

the most(that you can get) with min(it's not bullet proof) suffer

Dj

That is indeed what you were saying.

But what I was saying is that it is NOT the most you can get, nor the MIN hassle, and you don't HAVE to suffer, because

Replace(field, Position(field, "yellow", 1, 1), Length("yellow"), "red")

IS bulletproof.

Its only drawback is that for multiple occurences of yellow, you'd need to do

Loop

Set Field ["field", "Replace(field, Position(field, "yellow", 1, 1), Length("yellow"), "red")"]

Exit Loop If ["not Position(field, "yellow", 1, 1)"]

End Loop

Well if you really want to Replace all occurrence than try this one :

theField=

case(flag,case(not isEmpty(fieldWithValueToUseForReplace),Replace(GetField("theField"), Position(GetField("theField"), "fieldWithValueToUseForReplace", 1, 1), Length("fieldWithValueToUseForReplace"), "red"),GetField("theField")),field)

the bad side is that the above execute only twice for every update of field flag (good news is that if you havre only two occurence of fieldWithValueToUseForReplace inside the field than you don't need any script) so you would need an script like this one:

Freeze window

SetField [flag,1]

SetField [flag,1]

SetField [flag,1]

SetField [flag,1]

SetField [flag,1]

SetField [flag,1]

SetField [flag,1]

SetField [flag,1]

SetField [flag,1]

SetField [flag,1]

the above will update theField for up to 20 occurence of ieldWithValueToUseForReplace inside field

Dj

Thanks, but I think I still prefer

Loop

Set Field ["field", "Replace(field, Position(field, "yellow", 1, 1), Length("yellow"), "red")"]

Exit Loop If ["not Position(field, "yellow", 1, 1)"]

End Loop

Like RobinG suggested.

It's shorter and simpler and it is not limited to a finite number of occurrences of the search text.

Don't get it wrong, the scripted solution that you gave is the best solution.

The approach that I gave is playing around with FM and auto referencing.

Dj

Charmaine -- you're right, it doesn't work as I suggested. (I must be confucing it with the Exact function where converting to lower makes it case-insensitive.)

Which surprises me -- no, not the fact that I'm wrong; the fact that it doesn't work as I expected. There's got to be a way to make it happen... I'll have to think about it.

And yes, I did confuce the Replace function with the command. All in all, not a good response from me! My apologies.