Splitting a field name

[Anonymous]

Hi all,

I am having an issue with loading a field name as two separate fields.

Basically, the field name 'Version' is made up of two strings separated by a space. The length and format of both strings can differ and in terms of length and character composition (mix of numbers and letters).

I have tried to use the Trim and Left/Right functions to split Version into two different fields Version_Type and Version_Number taking the two strings, but because the number of characters differ in each item I can't apply a consistent rule.

The fields appears like this:

Version
DWSZPL X425
LTRB 31JK89
WSRTBBX 90D2Y
JTM PW8

I need to cut them at the space and load them as follows:

Version Type	Version Number
DWSZPL	X425
LTRB	31JK89
WSRTBBX	90D2Y
JTM	PW8

Any suggestions on the best way to do this?

Thanks in advance.

[Gysbert_Wassenaar]

subfield(Version,' ',1) As [Version Type]

subfield(Version,' ',2) As [Version Number]

[Anonymous]

Your solution is the subfield() function:

subfield(Version, ' ', 1) as "Version Type"

subfield(Version, ' ', 2) as "Version Number"

[Anonymous]

Thank you both for the quick replies.

I seem to be getting null values. I have seen that here may be some data items which contain two or three spaces, i.e. the Version Type portion would be made up of two or three strings. E.g:

Version Type	Version Number
CFFR WDS	8UHJ
CQ PFTH ZSD	BNHAS

Would this cause potential issues when using subfield in this manner or should it still display results even if not completely accurate?

The final space (if there are multiple) will always be the separator between Version Type and Version Number.

[Peter_Cammaert]

What about:

mid(Version, 1, index(Version, ' ', -1)-1) AS [Version Type],

mid(Version, index(Version, ' ', -1)+1) AS [Version Number],

[edit] or:

left(Version, index(Version, ' ', -1)-1) AS [Version Type],

mid(Version, index(Version, ' ', -1)+1) AS [Version Number],

[edit2] Indeed, omit the last space...

[robert_mika]

TextBetween(Version,'',' ') As [Version Type]

TextBetween(Version,' ','') As [Version Number]

Examples:

[giakoum · Accepted solution]

This is a small addition to make it work for the last space :

=subfield(Version, ' ', SubStringCount(Version, ' ')) as [Version Type]

=subfield(Version, ' ', SubStringCount(Version, ' ')+1)  as [Version Number]

[Anonymous]

OK, in this case subfield() is good only for the Version number:
subfield(Version, ' ', -1) as "Version Number"

As for the Version Type, it could be
left(Version, index(Version, subfield(Version', ' ', -1))-1)

[Anonymous]

Thanks Ioannis.

This has sorted it.

[#10]

Better ask the source team to send the file in fixed width or delimited by some symbol.