Uppercase last line of an address field ?

[AussieLad]

Our Creditors system allows for free-format entry of data into our "Address" field... eg,
8 Hart St
Newtown

What I am trying to do is to create a formula to display the last line of each Address as Uppercase, (as our data has a mixture of Uppercase and Propercase).
ie, in short, everything after the last Carriage Return of each field to be Uppercase. In the example above, I want...
8 Hart St
NEWTOWN

I know it's probably simple, but help !!

 

[lyanch]

I am still trying to devise psychic SQL so that I can read peoples "intent" when they put the data in. That way I can quit getting that dumb look from people when I say "you can't get there because of your data" and they go sure you can just "look at it" its easy to tell which is which


Lisa

 

[synapsevampire]

Aussielad: The final scenario is basically bad data, which is very difficult to resolve. My old company used to correct mailing lists as part of our services, and I would address your issue by using an address standardizing piece of software, common in the US, you may have something there to do the same.

Some basic thoughts:

-Flog the dba/developer who decided to combine fields into one
-Correct the application which does this
-If you can't do the above, consider writing SQL to correct the data on the database side in either a SP or View.
-In Crystal you might make assumptions about the data in the previous formula by adding to it that if there isn't a 3rd address line, take everything after the last space as an assumed address 3. Dangerous stuff...

-k

 

[pelajhia]

Can we really call that bad data? It seems more like the table should have had two or more fields to begin with.
Bad database! Bad!
I do like this idea: Flog the dba/developer who decided to combine fields into one.

 

[Naith]

I don't. When DBAs make create large fields intended for freetext, it's usually because he was explicitly asked by a user or the business to do so - and then when it doesn't turn out to have been a good idea 6 months later, there's calls for his scalp and for flogging. It's just not fair, I tells ya.

You're right, though, Pelajhia, it is the datasource which has been poorly thought out as opposed the the data itself - but I guess we all mean the same thing.

Naith

 

[Madawc]

Where I work, we have distinct fields for Street, District, Town, County and Postcode. Also the input logic insists on something for Street, Town and Postcode. Most entries are excellent, but some are bad, especially with older data.

Madawc Williams
East Anglia
Great Britain

 

[Ngolem]

AussieLad -

dgillz's idea of "Test for the existence of the chr(13) with an if-then statement: "

is a good one for this case...but surely there is something like a comma to separate the city/province(state) from the main body of the address....if there isn't then you are hooped as Madawc points out.

Naith - I am with SV on the DBA flogging...he/she should know the perils of treating an address that way and argue forcefully against it. It is kinda like a war crime defence to say "I was just following orders"...lol




Jim Broadbent

 

[AussieLad]

Thanks for all your input... one feels it's time for some data cleansing, or convincing 'Finance' to uppercase the entire field, (as per Jim Broadbent's very first suggestion).

I'll pass on the sentiments to our software vendor's developer as suggested... or maybe not ;-)

Cheers