Logic to split a column 100 characters long into 4 chunks...

[Minhajuddin]

Hi,

I am stuck with a tricky scenario. I have to split a column 'customer_name' which is 100 characters long into 4 chunks. The tricky part is that I am supposed to split it at spaces and also, then max number of characters in a chunk shouldn't be greater than 35. Is there a way we can do this in Datastage.

Any help is aprreciated.

Thank you.

[kcbland]

Transformer stage, use stage variables to help parse the text.

[us1aslam1us]

Look into the Index() function.....

[ray.wurlod]

If volumes are not large consider using a BASIC Transformer stage with either a Fold() or a Fmt() function.

[Maveric]

Since the name has to be split at spaces why not field() and then len() to find the length of the string. what should be done if the length is more than 35? Ignore the space and split it at 35th character?

[ArndW]

Code: Select all

INDEX(In.Col[1,35],' ',DCOUNT(In.Col[1,35],' ')-1)

will give the position of the last space before position 35. Assign from position 1 to this number to the first output string, then repeat (in stage variables) for the following 3.

[gaurav_shukla]

Hi,

I am stuck with a tricky scenario. I have to split a column 'customer_name' which is 100 characters long into 4 chunks. The tricky part is that I am supposed to split it at spaces and also, then max number of characters in a chunk shouldn't be greater than 35. Is there a way we can do this in Datastage.

Any help is aprreciated.

Thank you.

We can achive such functianlity by using Transformer and Stage Variables.
In Stage Variable cut the column by using Left and Right Function like
Left(Column_Name, 25). Use such function in four stage variables and cut the column accordingly.

Hope this helps

[ArndW]

guarav - the original post states that words cannot be split, which is what doing a straight substring extraction would do.

[gaurav_shukla]

guarav - the original post states that words cannot be split, which is what doing a straight substring extraction would do. ...

In that case your's solution is correct.
I understand it wrongly!