Hi Björn,
Assuming the ID consists of digits only and there are no other digits in the surrounding text, this can be handled using a regular expression.
I've set up an Excel sheet with some sample data. In my case, I've added a special character in there as well.
The derived business collection looks like this:
This business collection has a couple of (test) fields added.
IDText
This field contains the ID as a string and is set up using a regular expression.
# Texts.Text.RegexSubString("[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]")
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# A group getting exactly 8 digits.
#
# Texts.Text.RegexSubString("[0-9]+", 0)
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Picking the first match matching the digits-group of any length (the +).
# Indexing is zero-based and with 0 being the default, it is omitted in
# the expression below.
#
# The subString is added to indicate that the string will
# be no longer than 10 characters. Otherwise the field-type is Memo.
Texts.Text.RegexSubString("[0-9]+").SubString(0, 10)
The regular expression used finds all groups containing one or more digits. It picks the first matching group.
ID
This is the number you are looking for and is basically the same expression, but with the result converted to a number (in this case an integer).
This is probably the only field you are interested in for your scenario.
# Texts.Text.RegexSubString("[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]")
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# A group getting exactly 8 digits.
#
# Texts.Text.RegexSubString("[0-9]+", 0)
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Any number of digits by picking the first match matching the digits-group.
# Indexing is zero-based and with 0 being the default, it is omitted in
# the expression below.
#
# In case the number doesn't exceed the integer range, use the
# ToInteger() method. Otherwise, the ToLong() could be used
Texts.Text.RegexSubString("[0-9]+").ToInteger()
PartBeforeID
In case you are interested in other parts in the string, you can find all groups matching non-digits.
In our case, we are looking for the first group matching non-digits, which leads to the part before the ID.
# Texts.Text.RegexSubString("[^0-9]+", 0)
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Gets the first element not matching the digits group.
#
# Indexing is zero-based and with 0 being the default, it is omitted
#
# The subString is added to indicate that the string will
# be no longer than 30 characters. Otherwise the field-type is Memo.
Texts.Text.RegexSubString("[^0-9]+").SubString(0, 30)
Note the use of the ^ character in the regular expression, to denote a
not, yielding groups not matching the digits.
PartAfterID
A similar expression can be used to retrieve the second group not containing digits. In this specific case, this would lead to the group following the ID.
# Texts.Text.RegexSubString("[^0-9]+", 1)
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# gets the second first element not matching the digits group.
#
# Indexing is zero-based, so this picks the second group
#
# The subString is added to indicate that the string will
# be no longer than 30 characters. Otherwise the field-type is Memo.
Texts.Text.RegexSubString("[^0-9]+", 1).SubString(0, 30)
Those fields are probably not of interest but added for completeness.
Browsing the collection:
So, in your case, you can use the regular expression shown in the ID field:
Texts.Text.RegexSubString("[0-9]+").ToInteger()
In your situation this would be:
Fremdschluessel.[Frm Key].RegexSubString("[0-9]+").ToInteger()
Hopefully, the assumption about the digits is correct (ID consists of digits only, the other parts do not contain digits).
In that case, the above expression should do the job.
The project that was set up for this example has been attached.
Regards,
Frank