0

I have a report showing text data in a field from SQL Server database. Sometimes users need to put special code at the end of the text, so they put that code between two $. In the SSRS report I need to hide that code and the two $ and show only the text itself.

For example if I have the below text from SQL database:

My Name is AAAA. $z123$

I need it to be seen as:

My Name is AAAA.

Currently I am using the below code in SSRS, but it is only working if the text has the special code. If the text doesn't have the special code it is showing an error:

=Mid(Fields!DESCRIPTION.Value ,1,InStr(Fields!DESCRIPTION.Value ,"$") - 1)
Amr Magdy
  • 19
  • 4
  • maybe write an expression to catch if your pattern exists first, something like regex: "$.+$" – Mike_Jr Aug 29 '18 at 09:49
  • I used it like below, but it is still showing error when no special characters are found: =IIf( ( System.Text.RegularExpressions.Regex.IsMatch(Fields!Event.Value, "$.+$")) = False, Fields!Event.Value, Mid(Fields!Event.Value ,1,InStr(Fields!Event.Value ,"$") - 1)) – Amr Magdy Aug 29 '18 at 11:04
  • If a day has two rows the one with the special character is shown correctly, while the other shows error. – Amr Magdy Aug 29 '18 at 11:13

1 Answers1

0

I did it from SQL query instead as below:

ISNULL( STUFF(convert(nvarchar(max),E.DESCRIPTION),CHARINDEX('$',convert(nvarchar(max),E.DESCRIPTION)),CHARINDEX('$',convert(nvarchar(max),E.DESCRIPTION))+1,''),convert(nvarchar(max),E.DESCRIPTION))
Amr Magdy
  • 19
  • 4