Split string into seperate fields using space as delimiter

[Bunting]

I have a string field that consists of a customers full name, for example "Mr Stephen Harrisson" I need to display the above example in three seperate fields ie:
"Mr"
"Stephen"
"Harrisson"

Can anyone tell me how to separate the above?

 

[raja2008]

You can use Stringextract

 

[lbass]

//{@address}:
stringvar array x := split({table.fullname}," ");
x[1]

//{@firstname}:
stringvar array x := split({table.fullname}," ");
if ubound(x) >= 2 then
x[2]

//{@lastname}:
stringvar array x := split({table.fullname}," ");
if ubound(x) >= 3 then
x[3]

-LB

 

[raja2008]

For the first piece

ExtractString(table.string,""," ")

For the second piece

ExtractString(table.string," "," ")

Is the Full name always three pieces.

 

[Bunting]

Hi Ibass / Raja.

No the full name is not always three pieces and this is the challenge I am currently facing with the solution posted by Ibass.

Many thanks in advance for your help.

 

[bdreed35]

Bunting:

Please post some examples of your data that will show the possible scenarios that you will face. If you provide good examples, the experts can get you examples quicker than if you make them guess.

~Brian

 

[Bunting]

Just returned from holiday!!

Examples of data include:

Mr Stephen Harrison
Mr Stephen J Harrison
Mr S Harrison
Mr S J Harrison

The following scenario presents an additional problem whereby two spaces exist, i.e.
Mr S Harrison

 

[CR85user]

If the examples above were split into three fields would it be as follows?

1) Mr
2) Stephen
3) Harrison

1) Mr
2) Stephen J
3) Harrison

1) Mr
2) S
3) Harrison

1) Mr
2) S J
3) Harrison

Also are any entries only two parts - e.g. - Mr Smith?

'J

CR8.5 / CRXI - Discovering the impossible

 

[GJParker]

If you need a field for each part of the name then the only way is to create a separate formulas for each part and use LB's formulas to test the string and split out where necessary

i.e.

//{@Formula1}:
stringvar array x := split({table.fullname}," ");
x[1]

//{@Formula2}:
stringvar array x := split({table.fullname}," ");
if ubound(x) >= 2 then
x[2]

What is the overall purpose of what you are trying to achieve ? there may be a better solution.

HTH




Gary Parker
MIS Data Analyst
Manchester, England

 

[Bunting]

Thanks to all for the responses.

We have been asked to output a file from our database into a rigid template; for each record that we output we MUST populate the 'Surtname' field within the template.

However the problem we have is that the surname could be stored in one of two places; either the 'Surname' field, or as part of the 'Full name' field.

If the 'Surname' field has a value then we want to use this, else we need to extract the surname from the 'Full name' field.

I appreciate that this is not going to be wholly accurate, however I would be comfortbale with the output if I could evaluate the 'Fullname' field to use all characters after the last space.

I hope this makes sense

 

[GJParker]

Try this

Split({MyTable.FullName}," ")[Ubound(Split({MyTable.FullName}," "))]

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

Then use a formula like this:

//{@surname}:
stringvar array x := split({table.fullname}," ");
if isnull({table.surname}) then
x[ubound(x)] else
{table.surname}

-LB

 

[CR85user]

Formulas for Title and Surname could also be done as follows;

//{@Title}
extractstring(tbl.fullname},'',' ')

//{@Surname}
if isnull({tbl.surname}) then
strreverse(extractstring(strreverse({tbl.fullname}),'',' '))
else {tbl.surname}

Same end result but different method.

I will knock up something to check for Intitals / 1st name etc.

'J

CR8.5 / CRXI - Discovering the impossible

 

[CR85user]

Check for initials or first name:

//{@Mid}
if len(replace({tbl.fullname},' ','')) < len({tbl.fullname})-1 then
trim(mid({tbl.fullname},instr({tbl.fullname},' '),len({tbl.fullname})-(instr({tbl.fullname},' ')+instr(strreverse({tbl.fullname}),' ')-1))) else ''


'J

CR8.5 / CRXI - Discovering the impossible