Subfield Issue

[kicchu465]

Hi all,

I have a query regarding subfiled.

Please find the below Input data.

Name:

ABC D EFG

ABC XYZ

123 AB QWE

The Query is, I need to split the above data into three different filed using Subfield function or if there any other way to acheive this.

Reagrds,

Kb

[swuehl]

Maybe like

LOAD     

     Subfield(NAME, ' ',1) as FIELD1,

     Subfield(NAME, ' ',2) as FIELD2,

     Subfield(NAME, ' ',3) as FIELD3

FROM ....;

[Chanty4u]

Here with one more example

Subfield

[kicchu465]

Hi Stefan,

Thanks for your reply.

By using the above code the result would like as below.

Old_name	FIELD1	FIELD2	FIELD3
123 AB QWE	123	AB	QWE
ABC D EFG	ABC	D	EFG
ABC XYZ	ABC	XYZ

My  expected output should be like

Old_name	FIELD1	FIELD2	FIELD3
123 AB QWE	123	AB	QWE
ABC D EFG	ABC	D	EFG
ABC XYZ	ABC		XYZ

Thanks,

Kb

[swuehl]

What determines that the two part string need to be split and assigned to FIELD1 and FIELD3 instead of FIELD1 and FIELD2? Is this a general rule or based on the string content?

[sunny_talwar]

May be this

Table:

LOAD Name,

Subfield(Name, ' ', 1) as Field1,

If(SubStringCount(Trim(Name), ' ') = 2, Subfield(Name, ' ', 2)) as Field2,

Subfield(Name, ' ', -1) as Field3;

LOAD * Inline [

Name

ABC D EFG

ABC XYZ

123 AB QWE

];

[kicchu465]

Hi Stefan,

The rule is splitting a Name into forename,Middle Name and Surname.

As we know some people will use their Middle name in their name and few will not.

So I wanted to split the Name Field into  forename,Middle Name and Surname.

Thanks,

Kb

[Miguel_Angel_Baeyens]

I agree with Stefan, you need to determine which field stores what. My first name (Miguel Angel) is two words separated by a space, not a dash, very common in Spain and other Latin American countries, and I don't have a middle name. My last name (Baeyens de Arce) has 3 words (which again is very common in Spanish speaking countries), separated by space as well, and they all should go in the "LastName" field.

Just using SubField(), it would display wrong and incomplete following that logic: (first name Miguel -wrong-, middle name Angel -wrong-, last name Baeyens -wrong-, second part of last name "de Arce" missing -wrong-).

Even if you had a list of possible names, how can the application "guess" which part belongs to the first (composite or single), middle and last, and how long the last name can be (e.g.: one, two, three or more words)?

If you are splitting the names to have less unique values and optimize performance, that's OK, but on the front end you will have to concatenate them again. If one of those fields is empty, replace by a space, otherwise display the value.

[kicchu465]

Hi Sunny,

I have tried the above code and it has worked.

I have also created one more for resolving this please find below.

FullName:

LOAD * Inline [

Name

ABC D EFG

ABC XYZ

123 AB QWE

];

Result:

Load *,

SubField(Name, ' ',1) as Sub_FirstName,

MID(Name,index(Name,' ',1)+1,index(Name,' ',2)-(index(Name,' ',1)+1)) as Sub_MiddleName,

SubField(Name, ' ',-1) as Sub_Surname

Resident FullName;

Drop Table FullName;