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 SSRS 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 charts, tables, grouping, and format tables in SSRS.
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.”
=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). For more functions >> Click Here!
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.
=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.
