LIKE Search(please help ASAP)

Anon

Hi,

im not sure whether this is a basic kind of problem but I would like you to make any suggestions how you would solve this thing.

There are all different regions(in a country). I have all the rules how to figure out a REGION out of a postal code(always 4-digit). This is an example(VBG and STM are two different regions)

VBG:

67.. - 69..

STM

7421
80.. - 803.
830. - 836.
84.. - 89..
9323

Could anyone suggest a SQL Query when I want to list all entries of a region? I think it should work somehow with LIKE but I dont know how to code those dots(.) Each stands for a number(0-9). Thank you a lot!

toma42

do you have two tables named vbg and stm? If not, what is the data in your post? Post your table structure so we'll know what your query could look like please.

As I understand it, your question is: How do I do a query for all the codes from one region? is that right?

alastair

It is not too clear how you are storing information but...

if you used 'LIKE 80__' (note: 2 '_' underscores ) then that will match 8000 - 8099 (and 80aa-80ZZ) so you don't want that - do you??

how about changing the information you have to replace dots on the left of the hyphen with 0s and dots on the right with 9s and doing a greaterthan/lessthan comparison

kparker

If the mapping of codes to regions is discontinuous, you may have to resort to a separate table relating codes to regions. I note in particular the gaps in the numbers for STM, and it doesn't look encouraging: are the post codes 804x through 829x assigned to some other region, or are they unused? If the former, a text-based search will become unwieldy. But take heart: the mapping table only requires 9999 rows, and hopefully the post office doesn't make changes too often.

Anon

if you can write the code to initially populate that table, then you already have everything you need so you don't have to use the table.......

kparker

But would you want to put that code into a sql query? I'd far rather be able to say:

  select info.* from info, map
  where info.postcode = map.postcode and map.region = 'STM';

wouldn't you? Or would you really prefer to write:

  select * from info
  where postcode = '7421'
  or (postcode >= '80' and postcode <= '803')
  or (postcode >= '830' and postcode <= '836')
  or (postcode >= '84' and postcode <= '89')
  or postcode = '9323';

AND have to edit your source code every time a postcode changes?

Not to mention, the first version is probably going to run faster too.