Combining Remove and Replace Function in MS SQL Report Builder

Question

A field gives me the following string:

#$TEXTA#$TEXTB#$TEXTC

I want to remove the #$-chars and seperate the values by a new line. In addition I want to replace special characters (&). In the end it should look like this:

TEXTA
TEXTB
TEXTC

So I use this code:

=Replace(Replace(Fields!My_Field.Value,"&","&"),"#$",Environment.NewLine)

The string starts with #$, so this code makes a new line in the beginning, which I want to avoid by using this code:

=Right(First(Fields!My_Field.Value, "DATASET"), Len(First(Fields!My_Field.Value, "DATASET"))-2)

I want to remove the first two chars before the replace function starts. My problem is, that I dont know how to combine these two functions to work properly.

Answer 1

Without testing this....

I think you should just be able to replace your reference to the field in your first expression, with your second expression, like this.

=Replace(
        Replace(
               Right(First(Fields!My_Field.Value, "DATASET"), Len(First(Fields!My_Field.Value, "DATASET"))-2)
               ,"&"
               ,"&"
        ),
        "#$",
        Environment.NewLine
        )