Selecting Text with Beginswith and Endswith

Panorama X
1

I have a database with NAICS codes that look like this.

42, 423, 4231, 42311, 423110,42312… 4247, 42471 etc.

I want to select all that start with 42 thru all that start with 4247

Any suggestions how to do this?

2

No problem when your Code field is a text field.

3

It is a text field, maybe I am having a “duh” moment but I can not figure out how to do this. I have tried beginswith 42 or beginswith 4247 but the result is including 425, 4251,42511,425110, etc.

4 
(code +"00")[1,4] ≥ "4200" and (code +"00")[1,4] ≤ "4247"
5

How about

Select vCode[1,2]=“42” and vCode[1,4] < “4248”

this would work with a numeric for sure….without the quotes….

or

Select vCode beginswith “42” and vCode < “4248”

or

Select vCode beginswith “42” and val(vCode) < 4248

6

That one wouldn’t work because 425 is less than 4248, but the other two should.

7

Oops, yep…. I did not look close enough at the series…

8

Or maybe this would be better?

Select vCode[1,2]=“42” and vCode[3,3] < “5"

9

All those that work, work. If there is a better or worse, the better ones would be the ones that minimize the number of operations. Your

would be a good one or

code ≥ "42" and code < "4248"

would be another.

10

Good point about thinking about what “better” or “worse” actually means. If the data set is large, “better” probably means better performance. It’s not always obvious what the best performance is, you might have to test to know for sure. It might not always be the formula with the fewest operations, though that is probably the first one to try.

If the data set isn’t that large, “better” might mean “more understandable.” Sometimes the fastest one might be harder to understand, especially if you have to come back and make a change a year or two from now.

11

That points up the need for high-quality documentation of any procedure which does other than the simplest of tasks. I have always prefaced every discrete block of code with a brief comment describing what it’s going to do and this has saved me a huge amount of time when I’ve come back to it months or years later.

This has been both useful and necessary with some of Dave’s solutions. I recall writing several small essays describing how his implementation of the Sieve of Eratosthenes actually worked.