Page 1 of 1

Splitting text

Posted: 20 Aug 2020, 10:05
by tomvassie
Hello all,

I use the line information(JobNumber) field to record some information on an invoice,

The numbers could be as follow
1-1-1 ~ 1 up to 14-10-99 ~ 3000

Is there a way in the report to split these at the ~ and remove the ~ Leaving me with two parts to the JobNumber field on the report?

Thanks in advance

Re: Splitting text

Posted: 20 Aug 2020, 13:21
by brucedenney
You need to write expressions to extract the bit you want.

For the first part

Code: Select all

Substring(SALES_LEDGER.ANALYSIS_3, 0, IndexOf( SALES_LEDGER.ANALYSIS_3,"~"))
For the Second

Code: Select all

Substring(SALES_LEDGER.ANALYSIS_3, IndexOf( SALES_LEDGER.ANALYSIS_3,"~")+1,99)

Re: Splitting text

Posted: 20 Aug 2020, 17:08
by tomvassie
Hi Bruce,
Thank you, that’s absolutely brilliant.
I have been trying to workout substring all day but haven’t succeeded... will try later.

Is there any documentation regarding the operators and exactly to use them??

Thanks again

Re: Splitting text

Posted: 21 Aug 2020, 09:42
by brucedenney
There is a KB article on the sage website but it is not detailed.

https://ask.sage.co.uk/scripts/ask.cfg/ ... 78&DIV=SBD