Extracting a substring

[richard_chilvers]

I have a string such as:

08000355058|,803,|, National|,860,|, D1524|,881,|, M1529|,881,|, M1530|,881,|, M1531|,881,|

The 8** digits (eg. 803) are fixed identifiers to identify the values they follow.

In this example I want to ignore the '803' and '860' identifiers and preserve the '881' codes eg. D1524, M15329, M1530 etc.

I can use SUBFIELD to unpack the values during a LOAD, but I'm not clear how to get rid of the preceding string first, which I'm not interested in.

Any suggestions?

Thanks

[sunny_talwar]

Not really sure I understand what you are trying to do. Is your data coming from text file like a string? and you just want to extract this alpha-numeric code where it is followed by |,881?

[Clever_Anjos]

Please post your script here

[richard_chilvers]

Sorry it is quite hard to explain. But you are correct. Whenever there is an alphanumeric code between a comma and '|,881' I wish to extract it.

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD Trim(SubField(NewField, '|,', 1)) as FinalField

Where PurgeChar(SubField(NewField, '|,', 2), ',|') = 881;

LOAD SubField(Field, ',|,') as NewField;

LOAD * Inline [

Field

08000355058|,803,|, National|,860,|, D1524|,881,|, M1529|,881,|, M1530|,881,|, M1531|,881,|

] (delimiter is @);

[JonnyPoole]

load

  Code,

  Identifier

where Identifier='881';

load

  subfield(Row,'|',1) as Code,

  SubField(Row,'|,',2) as Identifier;

load

  SubField(Data & ',',',|,') as Row;

load * inline [

Data

08000355058|,803,|, National|,860,|, D1524|,881,|, M1529|,881,|, M1530|,881,|, M1531|,881,|

] (delimiter is ':') ;

[sinanozdemir]

It seems like you also want to pull the codes from the left side of the first 881. I believe you also don't want to hard code the numbers in Subfield() so you can use IterNo() with it.

Here is the data model:

Hope this helps.

[richard_chilvers]

Thanks - that's neat !

[richard_chilvers]

Thanks everyone for taking the time to look.

All suggestions seem to do the trick !