How to split column in to two columns

[#1]

Hi,

I have Address column like below

32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP

How can I get 32 Beach Drive, Scratby, Great mouth, folk, UK in one column and UY29 3NP in another column.

can anyone help me please.

Thanks.

[jagannalla]

Hi,

Load SubField(Address,',') as Address from table;

Sorry I think you need to us if condition.

Load

If (Address ='UY29' or Address='3NP',Address) as Col1,

If (Address <> 'UY29' and Address <> '3NP',Address) as Col2

from table;

Cheers!!

Jagan

[marcus_malinow · Accepted solution]

Hi Amelia,

you could try these two expressions:

Left(Field, index(Field, ',',substringcount( Field, ',')) - 1)

mid(Field, index(Field, ',',substringcount( Field, ',')) + 1)

Marcus

[#4]

Hi Amelia, Nagan

Subfield is OK but you have to specify the position of the comma you want to split.

If no position, default is 0 and will split as 32 Beach Drive only

So you need to split

SubField(Address,',', 5) to get the last field

to get the whole other part, you may trick with

left(your-string, len(your_string - len(SubField(Address,',', 5)-2))

len(SubField(Address,',', 5)-2))  this will gives you the len to remove from the original string

best regards

chris

[jagan]

Hi,

Try like this

For the first string

=Left('32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP', index('32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP', ', ', 5) -1)

For the second string

SubField('32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP', ', ', 6)

Regards,

Jagan.

[MayilVahanan]

Hi

Try like this

SubField('32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP', ',',-1)

SubField(Address,',', -1)

[alexandros17]

This is a general solution, hope it helps

LOAD * INLINE [ 

NUM,Address

1,"32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP" 

2,"42 Beach Drive, Scratby, Great mouth, folk, aaa, UK, vvvv"

3,"52 Beach Drive, Scratby, Great mouth, folk, bbb, ccc, UK, kkkk"

4,"62 Beach Drive, Scratby, Great mouth, folk, UK, zzzz"

]; 



EVAL:

NoConcatenate

LOAD *,

Left(Address, FindOneOf(Address, ',', SubStringCount(Address, ','))-1) as FirstPart,

Mid(Address, FindOneOf(Address, ',', SubStringCount(Address, ','))+1) as SecondPart

Resident Data;



DROP Table Data;

[rustyfishbones]

try

[#9]

Hi, Thanks very much.

I can able to get this.

[alexandros17]

Hi Amelia,

please copy my example, it works and is a general solution...

Let me know