Remove last carriage return from address string

[lsauser]

Hi all,

I have an address block like such;

123 Fake St
Suburb, VIC
4512

I want to only remove the last carriage return so it looks like;

123 Fake St
Suburb VIC 4512

I know i can use the following to get rid of all the carriage returns which does put it all on one line and i have played around with this with no luck getting it right. I have a feeling i need to use a combination of instr, strreverse and right but havent been able to get the logic to work.

replace({Header.Address},chrw(13),'')

As always, any help is greatly appreciated.

[Johan_Hakkesteegt]

Hi Nick,

Perhaps you can make the STRING_SPLIT function work for you:

SELECT value 
FROM STRING_SPLIT('123 Fake St
Suburb, VIC
4512', (CHRW(13)+CHRW(10)));

Regards,

Johan

P.S. If this is for use with SAP Business One, you should look into using the RDR12, DLN12, INV12 tables. These tables hold the separated address components.

[lsauser]

Hey Johan,

Thanks for the input on this one. Unfortunately i would prefer if this could be done with a crystal formula. I didnt write this particular report myself and the way they have done the commands and table joins is a bit beyond me.

A crystal formula will be the most efficient way for this one.

Regards,

Nick