How to separate the text value into one by one?

[#1]

hi all,

I have a address column with comma separated values like  The Business Centre,61 Wellfield Road,Roath,Cardiff

Can i separate this values into one by one with comma like,

The Business Centre,

61 Wellfield Road,

Roath,

Cardiff

If possible Can anyone explain how can i do this?

Regards,

Mani

[Anil_Babu_Samineni]

Yes, you can

Concat(fieldname,',',chr(10)) as newfield

[sunny_talwar]

I think all you need is this

LOAD SubField(FieldName, ',') as NewField

Read here about SubField function:

The Secret Life of SubField

[MarcoWedel]

Hi,

if you really want to keep the trailing comma, then one solution could be:

LOAD *,
    Replace(SubField(Replace(Address,',','@comma@,'),','),'@comma@',',') as AddressSubstring
INLINE [
    Address
    "The Business Centre,61 Wellfield Road,Roath,Cardiff"
    "QLIKTECH UK LTD, 1020 Eskdale Road, Winnersh, Wokingham, Berkshire"
];

hope this helps

regards

Marco

[Anonymous]

Use replace() and replace comma "," value to get your result .

Thanks,

[#6]

Hi  Marco Wedel thanks for reply...

This is my expected result but it is not working in text box and this script gets the values in ascending order like,

61 Wellfield Road,

Cardiff

The Business Centre,

Roath,

i want the actual result is showing in text box,,,,

Thanks,

Mani

[MarcoWedel · Accepted solution]

Replace(Address,',',','& Chr(10))

Regards

Marco