Can I remove character spaces from fields? 1

[urbanhim]

Our Oracle database stores all our clients addresses, if they make a change to address, we keep the new and the old.

I am building a report which looks at current address and compares it to previous entries, and shows where there is a difference.

Initially it would flag all difference when clients amended their upper/lower case, so I have got round this by doing following formula:

propercase ({ACCOUNT_ADDRESS.ADDRESS_1})

However, its now flagging clients have moved when really they have only removed and added a space character in their address.

For instance, one client has changed "NEWCASTLE UPONTYNE" to "NEWCASTLE UPON TYNE" and its saying he's moved, but obviously he has simply added a space. Can I write a formula which removes all spaces and then compares so this type of amendment would be ignored?

Thank you




UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[CR85user]

replace({ACCOUNT_ADDRESS.ADDRESS_1},' ','')

Should do it. BUT - I would warn of one thing.

propercase() would treat newcastle upontyne as:

Newcastle Upontyne

and would treat newcastle upon tyne as:

Newcastle UponTyne

With the spaces removed you then have two different entries:

NewcastleUpontyne
NewcastleUponTyne

I would compare them in lcase() or ucase() to simplify matters.

lcase({ACCOUNT_ADDRESS.ADDRESS_1})

or do both at once:

replace(lcase({ACCOUNT_ADDRESS.ADDRESS_1}),' ','')

'J

 

[urbanhim]

Hadnt thought of the Propercase problem, thanks for bringing that to my attention.

And this is brilliant, works perfectly...

replace(lcase({ACCOUNT_ADDRESS.ADDRESS_1}),' ','')

Thank you so much!



UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]