topic Re: Extract text from string in Discussions

Extract text from string

swiergi11 — Fri, 09 Jun 2023 18:08:37 GMT

Hi,

If possible I would like extract particular text from the strings:

\\swirt.misc@CAS-K-CLERK\K10447-Wit_OG lo hi TT-YYT (CTL1083.1)_172-7-10053 -303

\\swirt.misc@CAS-K-CLERK\K10445-Wit_OG lo hi ES WERT (CIL0858.1)_172-7-10046-600

\\swirt.misc@CAS-K-CLERK\K10437-Wit_OG lo hi TT-YYT (CTL1077.1)_172-7-10053-303

\\swirt.misc@CAS-K-CLERK\K10420-Wit_OG lo hi ES TRTR (CMA0851.1)_172-7-10047-600

\\swirt.misc@CAS-K-CLERK\K10377-Wit_OG_lo hi_ES_SI (CMS0499.1)_ 172-7-10041-600

\\swirt.misc@CAS-K-CLERK\K10451-Wit_OG lo hi TT-YYT (CTL1078

\\swirt.misc@CAS-K-CLERK\K10468-Wit_OG lo hi YYT DEV-020856 (CTLR1073

\\swirt.misc@CAS-K-CLERK\K10022 -Wit_OG lo hi YYT DEV-020414 (CTLR0999.1)

\\swirt.misc@CAS-K-CLERK\K10030-Wit_OG lo hi TT-YYT (CTL1008.1)

and I want to extract the IDs from above:

CTL1083.1

CIL0858.1

CTL1077.1

CMA0851.1

CMS0499.1

CTL1078

CTLR1073

CTLR0999.1

CTL1008.1

Any help appreciated.

Thanks,

Tomasz

Re: Extract text from string

pmroz — Tue, 11 Jan 2022 13:17:36 GMT

Use the WORDS function with ( and ) as delimiters. Use the second element in the returned list. Run the code below and check the output in the log window.

dt = New Table( "Test Words", Add Rows( 9 ),
	New Column( "Column 1", Character, "Nominal",
		Set Values(
			{
			"\\swirt.misc@CAS-K-CLERK\K10447-Wit_OG lo hi TT-YYT (CTL1083.1)_172-7-10053 -303",
			"\\swirt.misc@CAS-K-CLERK\K10445-Wit_OG lo hi ES WERT (CIL0858.1)_172-7-10046-600",
			"\\swirt.misc@CAS-K-CLERK\K10437-Wit_OG lo hi TT-YYT (CTL1077.1)_172-7-10053-303",
			"\\swirt.misc@CAS-K-CLERK\K10420-Wit_OG lo hi ES TRTR (CMA0851.1)_172-7-10047-600",
			"\\swirt.misc@CAS-K-CLERK\K10377-Wit_OG_lo hi_ES_SI (CMS0499.1)_ 172-7-10041-600",
			"\\swirt.misc@CAS-K-CLERK\K10451-Wit_OG lo hi TT-YYT (CTL1078",
			"\\swirt.misc@CAS-K-CLERK\K10468-Wit_OG lo hi YYT DEV-020856 (CTLR1073",
			"\\swirt.misc@CAS-K-CLERK\K10022 -Wit_OG lo hi YYT DEV-020414 (CTLR0999.1)",
			"\\swirt.misc@CAS-K-CLERK\K10030-Wit_OG lo hi TT-YYT (CTL1008.1)"}
		),
		Set Display Width( 583 )
	)
);

for (i = 1, i <= nrows(dt), i++,
	one_value = dt:column 1[i];
	
	word_list = words(one_value, "()");
	print(word_list[2]);
);

Re: Extract text from string

Byron_JMP — Tue, 11 Jan 2022 13:37:48 GMT

The solution from @pmroz is very robust, nice!

A little different approach might be to use formulas in the data table

The location of the start delimiter could be located with a "Contains" argument

Contains(:text, "(")

The location of the end delimiter could be located the same way

Contains( :text, ")" )

An obscure but really fun argument, "munger", could be used to extract the string between the delimiter locations.

In this case we want the string after the delimiter character, that ends before the ending delimiter character, so 1 is added to the start and subtracted from the end.

The function for munger in this case uses the arguments, whole text, where to start, how many characters to return. Munger can also do some other interesting things, like find and replace.

Munger( :text, :start + 1, (:stop - :start) - 1 )

Wrapping all that up into one formula would look like this

Munger(
	:text,
	Contains( :text, "(" ) + 1,
	(Contains( :text, ")" ) - Contains( :text, "(" )) - 1
)

Re: Extract text from string

pmroz — Tue, 11 Jan 2022 14:42:04 GMT

I use SUBSTR for similar effect.

for (i = 1, i <= nrows(dt), i++,
	one_value = dt:column 1[i];
	start  = contains(one_value, "(") + 1;
	end    = contains(one_value, ")") - 1;
	len    = end - start + 1;
	result = substr(one_value, start, len);
	print(result);
);

Re: Extract text from string

Mark_Bailey — Wed, 12 Jan 2022 16:01:25 GMT

In case you are into regular expressions... (Note: cleaned up hastily written code and replaced listing below on 1/12/22)

Names Default To Here( 1 );

// make list of example original strings
sample =
"\\swirt.misc@CAS-K-CLERK\K10447-Wit_OG lo hi TT-YYT (CTL1083.1)_172-7-10053 -303

\\swirt.misc@CAS-K-CLERK\K10445-Wit_OG lo hi ES WERT (CIL0858.1)_172-7-10046-600

\\swirt.misc@CAS-K-CLERK\K10437-Wit_OG lo hi TT-YYT (CTL1077.1)_172-7-10053-303

\\swirt.misc@CAS-K-CLERK\K10420-Wit_OG lo hi ES TRTR (CMA0851.1)_172-7-10047-600

\\swirt.misc@CAS-K-CLERK\K10377-Wit_OG_lo hi_ES_SI (CMS0499.1)_ 172-7-10041-600

\\swirt.misc@CAS-K-CLERK\K10451-Wit_OG lo hi TT-YYT (CTL1078

\\swirt.misc@CAS-K-CLERK\K10468-Wit_OG lo hi YYT DEV-020856 (CTLR1073

\\swirt.misc@CAS-K-CLERK\K10022 -Wit_OG lo hi YYT DEV-020414 (CTLR0999.1)

\\swirt.misc@CAS-K-CLERK\K10030-Wit_OG lo hi TT-YYT (CTL1008.1)";
sample = Words( sample, "\!U000D" );
// Show( sample );
n = N Items( sample );

// iterate over list and return target strings
id = List();
For Each( {string}, sample, Insert Into( id, Regex( string, If( Contains( string, ")" ), "(.+\()(.+)(\).*)", "(.+\()(.+)" ), "\2" ) ) );

Show( id );

Re: Extract text from string

txnelson — Tue, 11 Jan 2022 15:59:26 GMT

I like the "Word()" function for such string extractions

Names Default To Here( 1 );
dt = New Table( "Test Words",
	Add Rows( 9 ),
	New Column( "Column 1",
		Character,
		"Nominal",
		Set Values(
			{"\\swirt.misc@CAS-K-CLERK\K10447-Wit_OG lo hi TT-YYT (CTL1083.1)_172-7-10053 -303",
			"\\swirt.misc@CAS-K-CLERK\K10445-Wit_OG lo hi ES WERT (CIL0858.1)_172-7-10046-600",
			"\\swirt.misc@CAS-K-CLERK\K10437-Wit_OG lo hi TT-YYT (CTL1077.1)_172-7-10053-303",
			"\\swirt.misc@CAS-K-CLERK\K10420-Wit_OG lo hi ES TRTR (CMA0851.1)_172-7-10047-600",
			"\\swirt.misc@CAS-K-CLERK\K10377-Wit_OG_lo hi_ES_SI (CMS0499.1)_ 172-7-10041-600",
			"\\swirt.misc@CAS-K-CLERK\K10451-Wit_OG lo hi TT-YYT (CTL1078",
			"\\swirt.misc@CAS-K-CLERK\K10468-Wit_OG lo hi YYT DEV-020856 (CTLR1073",
			"\\swirt.misc@CAS-K-CLERK\K10022 -Wit_OG lo hi YYT DEV-020414 (CTLR0999.1)",
			"\\swirt.misc@CAS-K-CLERK\K10030-Wit_OG lo hi TT-YYT (CTL1008.1)"}
		),
		Set Display Width( 583 )
	)
);

For( i = 1, i <= N Rows( dt ), i++,
	Print( Word( 2, dt:column 1[i], "()" ) )
);

Re: Extract text from string

Byron_JMP — Tue, 11 Jan 2022 16:33:52 GMT

@pmroz , your code always looks so much less haphazard than mine ; )

Substring (substr) is a more straightforward approach, I just can't resist a good case for munger.

Craig Hales probably has some even more efficient approaches using pattern matching. I'm sure there are already some examples in the community pages or his blog page.

Re: Extract text from string

pmroz — Tue, 11 Jan 2022 16:36:55 GMT

Wow lots of ways to slice a loaf of bread in JMP.

Re: Extract text from string

swiergi11 — Tue, 11 Jan 2022 16:38:32 GMT

very nice, all of the suggestions works the magic.

I've used the Munger in my script and works perfect.

Thanks a lot

Tomasz

Re: Extract text from string

Craige_Hales — Tue, 11 Jan 2022 21:31:21 GMT

I like the simplicity of word() or words() when it will work. I wrote about munger here and no longer understand how to use it. I think it is in JMP because it was available on the Mac in 1990...not because it was an example of a good way to manipulate strings.

Re: Extract text from string

DonMcCormack — Tue, 11 Jan 2022 22:04:27 GMT

Pattern matching you say? How about this

Names Default to Here(1);

sample = "\\swirt.misc@CAS-K-CLERK\K10447-Wit_OG lo hi TT-YYT (CTL1083.1)_172-7-10053 -303
\\swirt.misc@CAS-K-CLERK\K10445-Wit_OG lo hi ES WERT (CIL0858.1)_172-7-10046-600
\\swirt.misc@CAS-K-CLERK\K10437-Wit_OG lo hi TT-YYT (CTL1077.1)_172-7-10053-303
\\swirt.misc@CAS-K-CLERK\K10420-Wit_OG lo hi ES TRTR (CMA0851.1)_172-7-10047-600
\\swirt.misc@CAS-K-CLERK\K10377-Wit_OG_lo hi_ES_SI (CMS0499.1)_ 172-7-10041-600
\\swirt.misc@CAS-K-CLERK\K10451-Wit_OG lo hi TT-YYT (CTL1078
\\swirt.misc@CAS-K-CLERK\K10468-Wit_OG lo hi YYT DEV-020856 (CTLR1073
\\swirt.misc@CAS-K-CLERK\K10022 -Wit_OG lo hi YYT DEV-020414 (CTLR0999.1)
\\swirt.misc@CAS-K-CLERK\K10030-Wit_OG lo hi TT-YYT (CTL1008.1)";

charSet = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.";

idList = {};
Pat Match(sample,"(" + Pat Span(charSet)>>nextID + (")" | "\!r") + Pat Test(Insert Into(idList,nextID);0));

It's unfortunate there isn't more support documentation for learning how to use pattern matching. I was a "Why bother with pattern matching when you have Regex?" person until I started to gain a better understand and appreciation for it (thank you @Craige_Hales!). It is blazingly fast and compact. To explain the code above, Pat Match is looking for an open parenthesis followed by one or more characters from charSet followed by the close parenthesis or a carriage return. The results from Pat Span are stored in the variable nextID. Each time the pattern is found, Pat Test is used to insert it into idList. The 0 at the end of Pat Test lets you continue searching the input string from the position immediately after the last character in the matched string (but before the next character). If the last statement in Pat Test had evaluated to true (anything not 0) the search would have ended, returning just the first match.

Re: Extract text from string

Mark_Bailey — Wed, 12 Jan 2022 16:12:39 GMT

Shh! @Craige_Hales, please don't tell @Byron_JMP. You will really hurt his feelings. (Thin skin I'm afraid)