troubleshooting Question

SSRS, search for specific characters in a string

Avatar of pensiongenius
pensiongeniusFlag for United States of America asked on
Visual Basic.NETMicrosoft DevelopmentSSRS
3 Comments1 Solution1922 ViewsLast Modified:
I have an SSRS report that returns a varchar field.  The source data is sometimes always in Caps and I have to change it to normal case in the report.  I use custom code to do this.

The current code I am using is below.

Public Function ConvertToTitleCase(inputStr) as String

  dim outputStr 

select case inputStr.Substring(0, 3) 

case "DFA"

   outputStr  = inputStr.Substring(0, 3) +StrConv(inputStr.Substring(3),vbProperCase)

       return outputStr

case else 

   outputStr  = StrConv(inputStr,vbProperCase)

       return outputStr

end select

End Function

As you can see I have an exception for when the first three characters are "DFA", however it has come to my attention that I may have other exceptions that I have to look for as well.  The problem is the other exceptions can come at varying places in the string so I can't look at a specific place.  I need some custom code that can look for specific characters in the string and keep them capitalized while the rest of the string is proper case.  Is this possible?

My plan is to first convert the entire string into proper case and then pass it through a case statement to look for these exceptions and switch the exceptions back to capital letters in the string.  

The exceptions will never actually be a word in the English language and there are no spaces in the exception words so I don't have to worry about accidentally capitalizing the wrong word, I just don't know how to parse through a string and find a specific set of characters in order.  For the record my exceptions are:

US (us will never show up as the word "us" it will always be capitalized)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros