11 Replies Latest reply: Aug 30, 2013 11:58 AM by Clever Anjos

# Convert Wildcards to Range of Records

I have a list of zip codes, some of which are wildcarded with '*'.  Each asterisk represents one digit.  For example:

9490* equates to 94900 through 94909

949** equates to 94900 through 94999

Example input:

zip

87124
9490*
123**

I would like the output to look like:

zip
87124
94900
94901
...
94909
12300
12301
...
12399

The "...", of course, are all the other zip codes that would be in the table.

Any ideas how I can go about doing this?

Cheers!

• ###### Re: Convert Wildcards to Range of Records

Hi.

Assuming that wildchars can occur in the end of the zip code, you could convert it like this:

zipwildchar,

if(zipwildchar=Previous(zipwildchar), peek(zip)+1, replace(zipwildchar, '*', '0')) as zip

INLINE [

zipwildchar

123**

9490*

87124

]

while iterno()<=pow(10,SubStringCount(zipwildchar, '*'));

• ###### Re: Convert Wildcards to Range of Records

Since I can assume that the asterisks are at the end (no potential of numbers between them), this works perfectly.  Thanks!

• ###### Re: Convert Wildcards to Range of Records

yourbase:

F1

87124

9490*

123**

];

for i = 0 to NoOfRows('yourbase') -1

let value = Peek('F1', i,'yourbase');

if Index('\$(value)','*') > 0 then // your value has at least one '*'

let vStart = Replace('\$(value)','*','0');

let vEnd = Replace('\$(value)','*','9');

Base:

\$(vStart) + RowNo() - 1 as Value

AutoGenerate \$(vEnd) - \$(vStart);

else

Base:

\$(value) as Value

AutoGenerate 1;

endif

next

• ###### Re: Convert Wildcards to Range of Records

Hi Clever,

For some reason the loops breaks 123** into a range of 12310 through 12409 instead of 12300 through 12399. The RowNo() in Base doesn't reset with each iteration in the loop.  Changing the order of the inline values changes the output with this script.  If RowNo() could be reset, this will work.

• ###### Re: Convert Wildcards to Range of Records

Try to replace RowNo() with RecNo(). The first one counts the resulting table while the second - the source table, the one that is to concatenate in this example.

• ###### Re: Convert Wildcards to Range of Records

That worked! Also, needed to add + 1 to the Autogenerate \$(vEnd) - \$(vStart).

• ###### Re: Re: Convert Wildcards to Range of Records

One more solution - see attached

• ###### Re: Convert Wildcards to Range of Records

It seems that it works only for two wildchars.

• ###### Re: Convert Wildcards to Range of Records

Actually this exaample is for one and two.  For three, there sholud be additional changes.

(Can place it all in an external loop... )

• ###### Re: Convert Wildcards to Range of Records

Yeah, I thought about that... There would be another LOAD to determine the maximum number of asterix.

I think the solution with loops could be more flexible.

For example without limitation that the wildchars should be in the end =)

• ###### Re: Convert Wildcards to Range of Records

Hi,

Use Recno() instead, Will fix this