Substring varying lengths

[sasha_pugliese]

I am trying to separate queries builds into different columns. For example below:

( country = 'US' AND  state IN( 'AZ', 'KY', 'WA') )  AND ( industry IN('Construction') )

Would like to have a Country Column, State Column, and Industry Column (there are others as well). The issue I'm having is that there are so many variations that I cannot just do a simple parse. Because state isn't always selected. So for example, I was trying to do a substring for State but when I do the following I get the error: "Type mismatch, String provided where a number is required"

if Contains([query],"state") then Substring([query],"state IN(", FindString([query],")")) else "" endif

My thought behind it was search for "state IN(" then end at the next ")". It seems that I have to put a number either in the start or length portion of the formula, I can't put string values in both. Any thoughts on how I could work around this? Since state could vary in length depending on how many states are selected I can't just choose a length.

[Claje]

Hi,

I think Regular Expressions might get you through this.  Any chance you could put up a couple dummy example records you are trying to parse, with some of the complexities you've outlined here?

[sasha_pugliese]

( country = 'US' AND  state IN( 'AZ', 'KY', 'WA') )  AND ( industry IN('Construction') )

( country = 'US' AND industry IN('Transportation') )

( country = 'US' AND state IN('AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY') ) AND ( industry IN('Business Services', 'Computers', 'Construction', 'Education', 'Energy', 'Finance', 'Government', 'Healthcare, Pharmaceuticals', 'Insurance', 'Legal', 'Manufacturing', 'Software') )

( country = 'US' AND ( industry IN('Manufacturing') ) AND revenue IN('$100 - 249 Million', '$250 - 499 Million') ) AND ( silo IN('Marketing')

( country = 'US' AND  code = '522310' AND industry IN('Business', 'Finance') ) 

There are additional fields that can be included in the query and there are some that may appear between industry and country. Thanks for taking a look at this!

[danrh · Accepted solution]

This might get you there:

A little convoluted, but it should be fairly dynamic.  If you need the other clauses in the future, just re-select them in the Select tool.