Text manipulation

[Dsnew]

Hi all,

I have a requirement where in I need to convert some values in a tables below.


From------------------------->To
===================================
111,22,3245,44 ------------> ##111,###22,#3245,###44
32,434-----------------------> ###32,##434
1232,3432,54,54334-------> #1232,#3432,###54,54334
.
.
.
===================================

Rule
- To pad the input data to a make it a total of 5 characters(left Pad the remaining with '#')
- The number of values in a coulmn is not fixed.
- The number of rows is not known.

I can convert the first value by using FMT and Field

FMT(Field(In.Value,',',1),"5'#'R")

How can I change it for all the values in the input?

Any inputs appreciated?

[chulett]

Build a routine. Count the number of fields and then loop through them, concatenating together the result.

[ArndW]

You will need to use a routine to do this, as Craig has already suggested. The reason is that loops are not supported in transform stages, and you will need to loop the conversion statement for each column.

[Dsnew]

Thanks Craig and ArndW for pointing in the correct direction.

[ray.wurlod]

Can be done without a routine.

Code: Select all

Convert(@FM,",",Fmts(Convert(",",@FM,InLink.TheString),"5'#'R"))

This converted the comma-delimited string to a dynamic array, applied the Fmts() function to the dynamic array element-by-element, then converted the dynamic array back to a comma-delimited string.

[chulett]

Cool, hadn't stumbled over that particular function before this. And no wonder, it's nowhere to be found in the online help. I guess it's time for you to once again advise that I read and commit to memory the entire DataStage BASIC Guide.