How to remove trailing spaces in SSRS report

Question

I have one stored proc which gives output with multiple spaces for few records.

I want to remove the extra trailing spaces from each record. I tried multiple things from internet, but no one gives me expected output.

Can someone help me here?

FYI, I designed it as

Answer 1

I would remove then already in the stored procedure using RTRIM functions.

But TRIM/LTRIM/RTRIM functions are also available in a report expression
See https://learn.microsoft.com/en-us/sql/reporting-services/report-design/expression-examples-report-builder-and-ssrs?view=sql-server-ver16#functions
=> String functions.

Answer 2

Hi @Akshay Chavan ,
Use the following Custom Code：

Public Function RemoveExtraSpaces(input_text As String) As String  
  
Dim rsRegEx as System.Text.RegularExpressions.Regex  
rsRegEx = new System.Text.RegularExpressions.Regex("\s+")  
  
return rsRegEx.Replace(input_text, " ").Trim()  
  
End Function  

Then set the expression as follows：

= Code.RemoveExtraSpaces( Fields!YourFieldName.Value )  

Here is my test:
Design:

Preview:

I had used the below VB code for defining the Regular Expressions class Regex;

Dim rsRegEx as System.Text.RegularExpressions.Regex  
rsRegEx = new System.Text.RegularExpressions.Regex("\s+")  

The regular expression used for removing the extra white spaces like extra spaces or multiple spaces is "\s+".
The below code replaces the multiple spaces characters with single space character and trims for the leading and trailing space characters using the Trim() function.

return rsRegEx.Replace(input_text, " ").Trim()  

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.