Alphabetically sort characters in a string

[Anonymous]

Hi Community.

I need your help. I would like to sort the characters in a string alphabetically in a new field in the load script.

For example:

Myfield      Newfield

'abc'          'abc'

'acb'          'abc'

'bca'          'abc'

'zxj'           'jxz'

'yzx'          'xyz'

The Myfield values are unknown so I can't specify various text values to replace in the function.

Any suggestions would be much appreciated.

[Gysbert_Wassenaar · Accepted solution]

Here's one way:

MyTable:

LOAD

     MyField, Concat(Letter,'',Letter) as NewField

GROUP BY

     MyField

     ;

LOAD

     MyField, Mid(MyField,IterNo(),1) as Letter

FROM

    ...source_table_here...

WHILE

     IterNo() <= Len(MyField)

     ;

[Anonymous]

You are awesome Gysbert, thank you!

RetRoute:

LOAD * INLINE [

    Rec, Cus, Br, Rev, Col, Del, MyField

    1, 1, 77, 100, HBN, Auck, HBN-Auck

    2, 1, 33, 2300, Nel, Has, Nel-Has

    3, 1, 33, 290, Has, Auck, Has-Auck

    4, 2, 22, 200, Has, Nel, Has-Nel

    5, 3, 77, 150, Auck, Has, Auck-Has

    6, 3, 88, 50, HBN, Nel, HBN-Nel

    7, 3, 77, 300, Nel, HBN, Nel-HBN

    8, 3, 77, 800, HBN, Auck, HBN-Auck

    9, 5, 44, 350, Auck, Has, Auck-Has

    10, 5, 55, 900, Has, Nel, Has-Nel

];

MyTable:

LOAD Rec,

     MyField, Concat(Letter,'',Letter) as NewField

GROUP BY

     Rec,MyField

     ;

LOAD Rec,

     MyField, Mid(MyField,IterNo(),1) as Letter

Resident RetRoute

WHILE

     IterNo() <= Len(MyField)

;

Drop table RetRoute

;

Edit: Script added