How to split column value to multiple column values in straight table

[#1]

Hi,

I have one address field

for eg: 12 Oak Tree Drive,Cave Hollod, Hadgoed ,Mid Glamorgan , , CF22 8TN

I need to split the above address as

Address1 = 12 Oak Tree Drive

Address2 = Cave Hollod

Address3 = Hadgoed

Town = Mid Glamorgan

County = ' '

PostCode = CF22 8TN

Could anyone help me please how to write the expressions in Straight table.

Thanks.

[#2]

Use subfield:

subfield(s, 'delimiter' [ , index ] )

In its three-parameter version, this script function returns a given substring from a larger string s with delimiter 'delimiter'. index is an optional integer denoting which of the substrings should be returned. If index is omitted when subfield is used in a field expression in a load statement, the subfield function will cause the load statement to automatically generate one full record of input data for each substring that can be found in s.

In its two-parameter version, the subfield function generates one record for each substring that can be taken from a larger string s with the delimiter 'delimiter'. If several subfield functions are used in the same load statement, the Cartesian product of all combinations will be generated.

Examples:

(For three parameters)

subfield(S, ';' ,2) returns 'cde' if S is 'abc;cde;efg'

subfield(S, ';' ,1) returns NULL if S is an empty string

subfield(S, ';' ,1) returns an empty string if S is ';'

The LOAD statement will look like this:

LOAD

               subfield(@1, ',', 1) as Address1

               subfield(@1, ',', 2) as Address2

               subfield(@1, ',', 3) as Address3

               subfield(@1, ',', 4) as Town

               subfield(@1, ',', 5) as County

               subfield(@1, ',', 6) as Postcode

FROM

Be aware that the comma can only be used as a delimiter.

[#3]

Thanks for your reply could you let me know in the subfield where can I use the columnname . The column is Home_address

[#4]

I used @1 for the columnname.

So replacing the first parameter in the subfield-statement by Home_address is what you're looking for.

[MayilVahanan]

Hi

LOAD

               subfield(Home_address, ',', 1) as Address1

               subfield(Home_address, ',', 2) as Address2

               subfield(Home_address, ',', 3) as Address3

               subfield(Home_address, ',', 4) as Town

               subfield(Home_address, ',', 5) as County

               subfield(Home_address, ',', 6) as Postcode

FROM TableName

[jagan]

Hi,

Try like this

LOAD

               subfield(Home_address, ',', 1) as Address1

               subfield(Home_address, ',', 2) as Address2

               subfield(Home_address, ',', 3) as Address3

               subfield(Home_address, ',', 4) as Town

               subfield(Home_address, ',', 5) as County

               subfield(Home_address, ',', 6) as Postcode

FROM TableName;

Regards,

Jagan.

[#7]

Thanks for your help but in the blank values it is showing Town,County and Postcode. Could you let me know how to adjust this please.

[#8]

Thanks for your help but in the blank values it is showing again the same problem. could you please help

[#9]

Thanks and sorry it's getting the same way as earlier. could you please help.

[MayilVahanan]

HI

Can you please attach sample