I would like my REPLACE not to be case sensitive ???

Question

I would like my REPLACE not to be case sensitive ???

jbalbo

SSCertifiable

Points: 7580
More actions
May 10, 2016 at 12:25 pm

#313847

Hi
I am using the following to change the font color for certain words but it seems to be case sensitive
=Replace(Replace(Fields!Report_of_Progress.Value,"cancel", "cancel") ,"no show", "no show")
I think I have to use Collate but not sure where??
Thanks
Joe

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 1

There doesn't appear to be a nice solution to case sensitivity in SSRS Repalce. COLLATE is a SQL clause - it's not accessible in an SSRS expression.

I suggest doing the relevant REPLACEs commands in your SQL query if at all possible, even if that means returning two Report_of_Progress columns, one having the values replaced. That would be a reasonably easy and reliable solution, although it could make your report considerably slower if you're dealing with large volumes of data.

An alternative, if your "cancel" and "no show" text only comes in two forms each, you could wrap your existing REPLACE statements with another two for the other two cases.

=

Replace(

Fields!Report_of_Progress.Value,

"cancel",

"cancel"

)

, "Cancel"

, "Cancel"

)

,"No Show"

, "No Show"

)

,"no show"

, "no show"

)

Otherwise, you could use Regular expressions to do the replace through custom report code and VBA, but that's a little more complex.

Spiff SSCertifiable Points: 7766 More actions · Answer 2

Replace can be made case insensitive by setting the CompareMethod to Text (it defaults to Binary which is case sensitive).

One thing that catches people out is that there are two other optional arguments before it in the function signature, Start & Count. So if you want to leave these as default but set the CompareMethod to Text you can name the argument using := , i.e.

=Replace("My Cased Text","text","String", Compare := CompareMethod.Text)

jbalbo SSCertifiable Points: 7580 More actions · Answer 3

Hi Spiff,

Although Andrew's way will work, if someone say puts in "NoSHow" it will miss it.

Just not sure how to set it up, really don't want you to do my work...:)

but can you show the syntax for

=Replace(Replace(Fields!Report_of_Progress.Value,"cancel", "cancel") ,"no show", "no show")

Thanks

Joe

Spiff SSCertifiable Points: 7766 More actions · Answer 4

This should do it, you just need to add the Compare := CompareMethod.Text to each replace function you use.

=Replace(

Replace(

Fields!Report_of_Progress.Value,"cancel", "cancel", Compare := CompareMethod.Text

)

,"no show", "no show", Compare := CompareMethod.Text

)

jbalbo SSCertifiable Points: 7580 More actions · Answer 5

Thanks spiff...

Works great, need to do some reading on compare

Thanks Again