Assistance with Replace

[tmccoy1]

I am modifying a report that uses a formula for a grouping and would appreciate any advise/explanation that anyone might offer. The formula is:

REPLACE(TRIM(REPLACE({tbl.prog_val},{tbl.prog_code},'')),'/','-')

I am new to the REPLACE function, but I believe that I follow its functionality in syntax, however I'm confused about the 2nd field name listed above. I read the above to be intended to be two nested REPLACE functions to remove spaces and the / and - characters, and I assume it would apply only to the first field listed. Can you apply a REPLACE to two fields in this manner?

 

[fisheromacse]

It looks to me like the formula is removing whatever value is returned by {tbl.prog_code} and replacing it with a nothing, then going through and removing the / and replacing them with -

REPLACE(
TRIM(
REPLACE(INPUT_STRING, FIND_STRING, REPLACE_STRING)),
FIND_STRING,REPLACE_STRING)

 

[tmccoy1]

Thank you. That is exactly what it was doing. It turns out that the prog_val field contained a concatenated value of the code and the value.

 

[pmax9999]

You can 'nest' replace so that it effectively replaces multiple parts of the string. In this example, the first (inner most) "REPLACE" will look for the prog_code string within the prog_val string and if it finds it, replace it with an empty string (if it exists). The second "REPLACE" will look for the "/" character and replace it with a "-". The TRIM function removes any spaces after the first "REPLACE".

I assume from this that the "prog_code" character string also appears within the "prog_val" character string, and that the code is designed to strip it out. If it doesn't, the first (inner most) "REPLACE" seems somewhat superfluous.

Hope this helps.

Cheers
Pete.