TM1 Forum

Hello Guys,

Good day.

I'd like to know how can I extract sub-strings from a string. Please see the following example:

PEPSI-MS-0870-3/PO:4500752654.30|NSW|SS

I would want to extract the 'NSW' and 'SS' from that string and store them in separate variables. How can I possibly do this in TM1? My senior said that I do a scan of the pipe.

Thanks in advance!

macklovesraine11 wrote:Hello Guys,

Good day.

I'd like to know how can I extract sub-strings from a string. Please see the following example:

PEPSI-MS-0870-3/PO:4500752654.30|NSW|SS

I would want to extract the 'NSW' and 'SS' from that string and store them in separate variables. How can I possibly do this in TM1? My senior said that I do a scan of the pipe.

Thanks in advance!

Are you able to provide an example you attempting to use SCAN function?

Sorry I haven't tried out yet.

macklovesraine11 wrote:Sorry I haven't tried out yet.

Have you at least read the documentation on the Scan and SubSt functions yet?

Do you want to extract NSW and SS from the string or do you want to dynamically extract the string after the first pipe and the string after the second pipe?

if you want to store NSW and SS in a variable then just say v1 = 'NSW' and v2 = 'SS'!

Of course I know this isn't what you want to do but it could be read in that way!

Assuming you want to find the string after the pipes and assuming the full string always includes 2 pipes and the string you want to extract always comes after the pipes and the string you want to extract after the first pipe is always 3 characters long and 2 characters long after the second pipe then something like:
may suffice.

But more information would mean less assumptions on my part!

cheers, mark

Mark RMBC wrote:Do you want to extract NSW and SS from the string or do you want to dynamically extract the string after the first pipe and the string after the second pipe?

if you want to store NSW and SS in a variable then just say v1 = 'NSW' and v2 = 'SS'!

Of course I know this isn't what you want to do but it could be read in that way!

Assuming you want to find the string after the pipes and assuming the full string always includes 2 pipes and the string you want to extract always comes after the pipes and the string you want to extract after the first pipe is always 3 characters long and 2 characters long after the second pipe then something like:

Code: Select all

vScan = subst('PEPSI-MS-0870-3/PO:4500752654.30|NSW|SS', scan('|', 'PEPSI-MS-0870-3/PO:4500752654.30|NSW|SS') + 1,long('PEPSI-MS-0870-3/PO:4500752654.30|NSW|SS'));

vScanNSW = subst(vScan,1,3);
vScanSS = Subst(vScan,5,2);

may suffice.

But more information would mean less assumptions on my part!

cheers, mark

Hi Mark,

Thanks for the input. I would want to dynamically extract sting after the first pipe and the string after the second pipe. Number of characters is not always 3 and 2, sometimes it can be 2 and 4, 3 and 3, 2 and 2. Something like that.

Thanks, hope this info helps
Michael

Hi Michael,

Consider the functions Scan, Subst, Long.
That should do it.

You haven't said where this string data comes from.
If it were to come as a text data source you might be able to set the delimiter to the pipe char which would split it automatically for you.

Well then you could add into the code above:
and change the following:
Assuming all the assumptions are valid!

Hi Guys,

Good day.

Thanks for all your inputs! I gained the idea based on your suggestions, it now gives the desired result and output looks good.

Thanks everyone!
Michael

Hi, thank you for the feedback, glad it's solved !