The SSRS InStr function is a Text function that searches for the character or substring inside the string field and returns the starting position of the first occurrence. This article explains how to use the InStr function to find the first occurrence of the substring starting position with an example.
The syntax of the SSRS InStr function to search and find the index position of the first occurrence of the text (substring) in a string is as shown below.
=InStr(Fields!Name.Value, " abc")
To demonstrate the SSRS InStr function, we use the Employee table below, which has 15. The image shows the records in the Table report.
SSRS InStr function Example
Let me add a new column to the right side of the Email_Address column. Next, right-click the textbox under the o Loc and choose the Expression. To understand the report, I suggest you refer to the articles on SSRS charts and SSRS table report from our SSRS tutorial page.
It opens the following expression window. The SSRS InStr function below searches for “o” inside the Email address column and returns the starting position of the first occurring “o.” Also, check out the SSRS grouping.
=InStr(Fields!Email_Adress.Value, "o")
Please click the preview tab to see the first occurrence position. Remember, the index position starts from 1 (not 0).
Let me try a substring(or string) instead of a single character as the search item. So, create a new column, and the InStr Function expression below searches for the “tu” string inside the email address and returns the starting position of the first occurring “tu.” If it isn’t found, it returns 0.
=InStr(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 SSRS InStr Function code below searches for empty space in the Name field and returns the first occurrence starting position.
=InStr(Fields!Name.Value, " ")
The below code searches for @ in the Email_Address field and returns the first occurrence starting position. Please refer to the SSRS INSTRREV function from the list of available SSRS functions.
=InStr(Fields!Email_Adress.Value, "@")
Please check the report preview. The Name and Mail columns have only one ” ” and “@” symbol, so it returns that position.
