Find a string value between 2 strings

Question

I have a database that I load with EntityFramework.

In this database there is a table that contains a couple of columns. 2 of those columns are called ZipcodeFrom and ZipcodeTill. The Zipcodes in the table are from different countries, so there is no real standard format.

The problem I have now is the following.

I receive a Zipcode and I need to find the row which contains the Zipcode range associated with the received Zipcode.

I can't use SQL to use the BETWEEN function. So I'm looking for a BETWEEN function for C# if (Zipcode BETWEEN ZipcodeFrom and ZipcodeTill)

rob, try this method from SO: http://stackoverflow.com/questions/4087311/equivalent-of-sql-between-statement-using-linq-or-a-lambda-expression or this one: http://stackoverflow.com/questions/1447635/linq-between-operator?lq=1 — jim tollan, Apr 23 '15 at 08:43
You could get all the data from the database and use LINQ to do the searching. — Manoj Reddy, Apr 23 '15 at 08:44
@jonamreddy loading an entire zipcode database is not a good idea, typically - perhaps unless you're using a file based library — Marc Gravell, Apr 23 '15 at 08:45
It really all depends on the format of the Zipcodes and when you define a Zipcode to be "between" two other zipcodes. For example: a dutch zipcode consists of 4 numbers followed by two letters. Would 1234BC be between 1234BB and 1234 BD? And would it be between 1234AC and 1234CC? The best way in my opinion would be to treat a zipcode as a base-36 number and do your work based on that, but do all zipcodes follow a format where they are build up out of just numbers and letters? — Nils O, Apr 23 '15 at 08:48
i agree with nils above. it really is academic as to how you ascertain which zipcodes form a linear sequence that allows you to group and extract a subset that lies between two bounds... — jim tollan, Apr 23 '15 at 08:52
As Nils O already stated. The data looks as followed: For NL we have 1234AA to 1234BB some zipcodes are only nummeric like the American zipcodes 6424 to 6427 — Rob v K, Apr 23 '15 at 09:25

DavidG · Accepted Answer · 2015-04-23T12:29:41.923

1

Assuming you are doing this alphabetically, this effectively mimics a SQL BETWEEN operation:

var zipCode = "SOMECODE";

var result = from row in db.Table
             where row.ZipcodeFrom <= zipCode
             && row.ZipcodeTo >= zipCode;

If your provider doesn't support using <, <=, > and >= operators, you should be able to use string.Compare instead:

var result = from row in db.Table
             where string.Compare(row.ZipcodeFrom, zipCode) <= 0
             && string.Compare(row.ZipcodeTo, zipCode) >= 0;

edited Apr 23 '15 at 12:29

answered Apr 23 '15 at 08:48

DavidG

113,891
12
217
223

The problem here is that when i do this the following error occurs because i'm comparing strings: Operator >= cannot be applied to operands of type string and string – Rob v K Apr 23 '15 at 11:22

Find a string value between 2 strings

1 Answers1