SSRS, search for specific characters in a string

Posted on 2013-09-03
Medium Priority
Last Modified: 2013-09-05
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:

US (us will never show up as the word "us" it will always be capitalized)
Question by:pensiongenius
LVL 40
ID: 39462428
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.
LVL 37

Accepted Solution

ValentinoV earned 2000 total points
ID: 39462751
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.

Author Closing Comment

ID: 39468193
This was exactly what I was looking for and the link to the article was very helpful.  Thanks for the quick fix.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Did you know PowerShell can save you time with SaaS platforms? Simply leverage RESTfulAPIs to build your own PowerShell modules. These will kill repetitive tickets and tabs, using the command Invoke-RestMethod. Tune into this webinar to learn how…

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question