SSRS, search for specific characters in a string

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

Open in new window


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:

PIMCO
II
III
TDA
JP
DFA
US (us will never show up as the word "us" it will always be capitalized)
pensiongeniusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jacques Bourgeois (James Burger)PresidentCommented:
Lookup the Instr method in the documentation. It returns the position of the first occurrence of a given string inside of another string, 0 if it is not found in the string.
0
ValentinoVBI ConsultantCommented:
As far as I understand your requirement it's probably an option to use the Replace function.

First beautify your string as you're doing now, then loop over all your exceptions and replace any occurrence with the capitalized version.  Don't forget to set the last parameter to CompareMethod.Text (which is case insensitive) to make sure you don't miss any.

See Replace Function (Visual Basic) for more info.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pensiongeniusAuthor Commented:
This was exactly what I was looking for and the link to the article was very helpful.  Thanks for the quick fix.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.