topic Re: how to extract string between specific pattern using regexp? in Discussions

how to extract string between specific pattern using regexp?

Shlomizr — Fri, 14 Sep 2018 09:17:22 GMT

Hi,
I am new to regex, i need to extract the name of users from their id, for example:
I have the following long string
123450_dan; 154396_kelli; 198756_dev;
Result need to be: dan
What regex do i need to use in order to extract the name dan which have the id 123450?

Thanks in advance.

Re: how to extract string between specific pattern using regexp?

Mark_Bailey — Fri, 14 Sep 2018 13:22:57 GMT

You did not specifiy much detail about the ID so here are a few examples to get started. I used a JMP script, but this extraction could be used in a column formula, too.

Names Default to Here( 1 );

// test string
string = "123450_dan; 154396_kelli; 198756_dev;";

// assume specific ID
name = Regex( string, "123450_(\w+\b)", "\1" );
Show( name );

// assume first ID with 6 numbers
name = Regex( string, "\d{6}_(\w+\b)", "\1" );
Show( name );

// assume first ID with any number of numbers
name = Regex( string, "\d+_(\w+\b)", "\1" );
Show( name );

Re: how to extract string between specific pattern using regexp?

Mark_Bailey — Fri, 14 Sep 2018 13:27:01 GMT

Assuming that you want fhe name associated with a specific ID in the parttern "id_name;", then this version will work, too.

Names Default to Here( 1 );

// test string
string = "123450_dan; 154396_kelli; 198756_dev;";

// target ID
id = "123450";

// assume specific ID
name = Regex( string, id || "_(\w+);", "\1" );
Show( name );

Re: how to extract string between specific pattern using regexp?

Shlomizr — Fri, 14 Sep 2018 13:56:07 GMT

Thanks Mark,
Working perfect!!

Re: how to extract string between specific pattern using regexp?

gzmorgan0 — Fri, 14 Sep 2018 18:13:52 GMT

Just for fun, and because regular expressions can be tough to read by others (or two weeks later), Here is an alternative, using JMP functions.

Names Default to Here( 1 );

// test string
string = "123450_dan; 154396_kelli; 198756_dev;";

//create a list of id_name
id = Words(string, ";");

//create a list if ids, a list if names, an associative array(keyed list) 
idlist={};
namelist={};
lookup = [=>""];  //Associative Array that retuns an empty string, if number is not valid

for(i=1, i<=nitems(id), i++,
   InsertInto(idlist, num(word(1,Trim(id[i]),"_")) );
   InsertInto(namelist, Titlecase(word(2,Trim(id[i]),"_")) ); //don't need TitleCase
   lookup[idlist[i]]=namelist[i]  //ids are the keys and names are the values
);
show(id, idlist, namelist, lookup, lookup[198756], lookup[128954]); //last one is not valid

Re: how to extract string between specific pattern using regexp?

Shlomizr — Sun, 16 Sep 2018 06:55:51 GMT

Hi Mark,
Just 1 question, what do i need to add if i have space in the user name:

for example: "123450_dan fox; 154396_kelli; 198756_dev;"
I tried the following Regex:

string="123450_dan fox; 154396_kelli; 198756_dev;"

name = Regex( string, "123450_(\w+ | \s+)", "\1" );
Show( name );

Result: "dan" instead of "dan fox"

Thanks in advance.

Re: how to extract string between specific pattern using regexp?

gzmorgan0 — Sun, 16 Sep 2018 09:17:59 GMT

The script that I provided previously using JMP functions would not require any modifications. The regular expression to capture one or more "names" after the underscore would be (\w+\s*)+ which means accumulate ( ) at least one word \w+, zero or more spaces \s* and the trailing plus implies repeat the pattern one or more times.

Names Default to Here(1);
string="123450_dan fox; 154396_kelli ann marie; 198756_dev;";

name1 = Regex( string, "123450_((\w+\s*)+\b)", "\1" );
name2 = Regex( string, "154396_((\w+\s*)+\b)", "\1" );

Show( name1, name2 ); 
/*: name1 = "dan fox";  name2 = "kelli ann marie"; */

Re: how to extract string between specific pattern using regexp?

Mark_Bailey — Sun, 16 Sep 2018 15:53:49 GMT

Use this form instead:

name = Regex( string, "123450_([a-zA-Z ]+);", "\1" );

Re: how to extract string between specific pattern using regexp?

Shlomizr — Mon, 17 Sep 2018 06:32:51 GMT

Thanks, work perfect.

Re: how to extract string between specific pattern using regexp?

Shlomizr — Mon, 17 Sep 2018 06:34:43 GMT

Thanks, work perfect!!