Search code examples
reporting-servicesreportbuilder

Remove first and last characters

A Field gives me the following string:

#$Mercury#$Venus#$Earth#$Mars#$

My goal is to remove the #$-chars and separate the text with comma and space. It should look like this:

Mercury, Venus, Earth, Mars

What I tried to do:

  1. Remove the #$-chars at start and end;
  2. Replace the remaining #$-chars with ", "

My code:

=Replace(
        Left(
            Right(
                First(Fields!FieldX.Value, "DATASET"), Len(First(Fields!FieldX.Value, "DATASET"))-2),
            Len(First(Fields!FieldX.Value, "DATASET"))-2),
        "#$",", "
        )

This gives me:

Mercury, Venus, Earth, Mars,

At the end after Mars there is a comma and it shouldn't be there. I don't know how to do that. Maybe the MID function is the better solution, but then I don't understand how it can be used when the strings are of variable length.

Thanks in advance.

Solution

  • This should work. I tested in a table so you will have to add the FIRST and "DATASET" bits back in )

    =JOIN(
    SPLIT(MID(Fields!SampleText.Value, 3, LEN(Fields!SampleText.Value)-4), "#$"),
    ",")

    All we do here is chop off the first and last 2 characters, then SPLIT on each instance of #S which gives us an array. Then JOIN the array elements back togther using , as the delimiter.

    enter image description here