The SSRS InStrRev function is a Text function that searches for the character or substring inside the string field from the right side and returns the position of the first occurrence. This article explains how to use the SSRS InStrRev function to find the last occurrence position of the substring with an example.
The syntax of the SSRS InStrRev function to search the substring from the right side and find the first occurrence position in a string is as shown below.
=IInStrRev(Fields!Name.Value, " abc")
To demonstrate the SSRS InStrRev function, we use the Employee table below, which has 15 rows. The image shows the records in the Table report.
SSRS InStrRev function Example
Let me add a new column to the right side of the Email_Address column. Next, right-click the textbox under it and choose the Expression. To understand the report, I suggest you refer to the articles on charts, tables, grouping, and format tables in SSRS.
It opens the following expression window. The SSRS InStrRev function below searches for “o” from the right side (reverse order) inside the Email address column. When it finds the first occurring “o,” it starts counting numbers from the left side to that position.
=InStrRev(Fields!Email_Adress.Value, "o")
Please click the preview tab to see the last occurrence position. Remember, the index position starts from 1 (not 0). For more functions >> Click Here!
Instead of a single character, try a substring(or string) as the reverse search item. So, create a new column, and the SSRS InStrRev function expression below reverse searches for the “tu” string inside the email address and returns the last occurring “tu” position. If it isn’t found, it returns 0.
=InStrRev(Fields!Email_Adress.Value, "tu")
Similarly, let me create two more new columns to the right of the Name and Email columns. Next, right-click the textbox under them to write the expression. The below InStrRev code searches for @ in the Email_Address field and returns the last occurrence position.
=InStrRev(Fields!Email_Adress.Value, "t")
The InStrRev code below searches for an empty space in the Name field from the right side and returns the first occurrence starting position.
=InStrRev(Fields!Name.Value, " ")
Please check the report preview. Since the” ” and “@” symbols repeat only once in them, the InStr and InStrRev functions return the same result.