CODEWITHSUNDEEP
stringt-sqlif-statementreporting-servicessubstring
YelizavetaYR
YelizavetaYR

Reputation: 1711

Find Sub string in SSRS and evaluate or replace

In SSRS I have data field called Event_dt it returns as Digital followed by Month, then Year. It looks like this Digital January 2021 or Digital May 2019

Question 1: In one field - I need to truncate the word Digital so the string would only read January 2021 or May 2019

Question 2: In the second string i need to evaluate, if Event_dt string contains the word Digital then it should output something else. I know the iff(part ... but i'm not sure how to get the sub string part in SSRS.

Upvotes: 2

Views: 3419

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10880

For Q1, replacing is fairly straight-forward. Use the REPLACE function and replace Digital with an empty string. Don't forget the space after Digital.

=Replace(Fields!Event_dt.Value, "Digital ", "")

For Q2, you don't say how you want to replace the text. To check if the field has a certain string, you can use the InStr function. It returns the starting position of the string to be found in the string. If it doesn't find the string in you field, it returns 0 (you check for 0 of not 0 if you just need to determine if it's there and not where). 

=IIF(InStr(Fields!Event_dt.Value, "Digital") > 0, <Has String Part>, <ELSE Part>)

In SSRS, the MID function is the same as SUBSTRING - it returns the characters from the start point indicated in the first argument and the number of characters to return in the second argument.

You could actually write the REPLACE with this - though it would be more complicated.

=IIF(InStr(Fields!Event_dt.Value, "Digital") > 0, MID(Fields!Event_dt.Value, 9, LEN(Fields!Event_dt.Value)), Fields!Event_dt.Value)

This would read as

IF the EVENT DT contains Digital (i.e. value returned from INSTR is greater than 0), then return the EVENT DT field from character 9 through the end of the string, otherwise return the EVENT DT field.

Upvotes: 1

Related Questions