Reversing abbreviations on Cities

I need to somehow be able to recognize when a user has input a city that has some sort of abbreviation, like St Paul, and change it to Saint Paul before it gets input into the program I’m using, because St paul is not in the database, but Saint Paul is.

So I have my variable “city”. How do I go about filtering this issue I’m having?

I may have to do this for multiple cities as well, but I don’t know what exactly is in the database, so it’s going to be a case by case issue.
So I will probably have to add other cities to filter out abbreviations in the future.

Any ideas on the best way to go about doing this?


I would suggest you create an excel sheet and define all the abbreviations in that.
Read the excel and assign the values. If the abbreviation matches the list then assign the corresponding value.
Use those values in your workflow which are available in DB.

Okay, so I’ll create an excel spreadsheet, I think that’s a good idea, so that we can add to it later. How do I go about filtering the string input from the user to see if the user entered st paul, or st george, etc… How do I filter out just the ‘St’ part of the cityName string, and then compare that to what’s in my excel file? What activities work best for doing that job.

I would suggest adding values like,
St paul = Saint Paul (As per DB)
St george = Saint George (As per DB)

Then take the key into one variable and match with value in excel file.
Activities - Excel application scope, read range, assign activity, decision activity.

I’m using a CSV file because I think it would be easier, and
What I’m doing is:

St, Saint
E, East
W, West
S, South
N, North

And I’m reading from the CSV into a data table called abbreviations.
Where row(0) is the abbreviation I want to replace,
And row(1) is the un-abbreviated name that I want instead
Then I use a Data table type “For Each” Activity,
And so far I’ve got…

for each row in abbreviations(my data table)


              Then change that part of the string to row(1)


Not sure how to go about doing that…