We help IT Professionals succeed at work.

Substring & replace function in case statement

Lalla Belle
Lalla Belle asked
on
Hi,

I need to write a formula, that will replace certain characters with other characters if it encounters them in a string.

What I want is something like this:

Select Case  {mydb_txt.Field1}
Case ("+®")
formula=UCASE(Replace ({mydb_txt.Field1},"+®","é"))
Case ("+Ë")
formula=UCASE(Replace ({mydb_txt.Field1},"+Ë","ç"))
End Select

It doesn't work however.

Please help
Comment
Watch Question

Top Expert 2010
Commented:
Why bother with a CASE statement?  Just replace the characters in one shot:

formula=UCASE(Replace(Replace ({mydb_txt.Field1},"+®","é"),"+Ë","ç"))
Your Select-Case is comparing the entire Field1 to those characters, so the replace functions will only be executed if Field1 exactly matches one of those strings.  And it would only execute one of the Replace functions, because only one option in a Select-Case will ever be executed.

 As Jay said, you could nest the Replace functions, if you don't have too many of them.  If there are too many, it may get hard to follow, in which case you could use a variable and do the replaces one by one.  Something like the following.  I'm using Crystal syntax, because that's what I'm used to.

Local StringVar tmp;

tmp := Replace ({mydb_txt.Field1},"+®","é");
tmp := Replace (tmp,"+Ë","ç");
// and so on for any other characters you're replacing
UpperCase (tmp)

 I put the Uppercase at the end to make sure it didn't alter any of the characters that you might be trying to replace.

 As I mentioned earlier, even if the Select-Case worked, it would only execute one of the Replace functions.  For example, if the first character was found, it would replace it, and not replace any other characters.  If that's actually what you wanted to do, you could do that, but it would probably be easier to just use if-else, instead of trying to use Select-Case.

 James