Find if a given postcode is in a range

Question

Post reply

Find if a given postcode is in a range

tina.muter

Valued Member

Points: 58
More actions
May 12, 2008 at 2:52 am

#71589

I have data provided by an external source in which they have a 'from postcode' and 'to postcode' fields. These identify postcode that a certain warehouse deals with.
For example
warehouse From To
1 DL1 1AA DL17 6YY
2 DL17 1YZ DL79 6AA
3 DL79 1AB DL99 6ZZ
If want to find DL7 6FF, you can't use 'between' at it will return warehouse number 2, when it is actually warehouse 1, with DL7 being before DL17.
I can't think of a way to deal with this. I had though of using some sort of calculated value to numerically represent the postcodes and search that way, but no sure if that is really feasible to do.
As suggestions greatly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RyanRandall SSChampion Points: 13623 More actions · Answer 1

I guess you could 'pad' that first number before you do your comparisons...

declare @t table (warehouse int, [From] varchar(10), [To] varchar(10))

insert @t

select 1, 'DL1 1AA', 'DL17 6YY'

union all select 2, 'DL17 1YZ', 'DL79 6AA'

union all select 3, 'DL79 1AB', 'DL99 6ZZ'

union all select 3, 'B1 1AB', 'B2 6ZZ'

declare @p varchar(10)

set @p = 'DL7 6FF'

select * from (

select *,

stuff([From], patindex('%[0-9]%', [From]), 0, replicate('0', 5-charindex(' ', [From]))) as FormattedFrom,

stuff([To], patindex('%[0-9]%', [To]), 0, replicate('0', 5-charindex(' ', [To]))) as FormattedTo

from @t) a

where stuff(@p, patindex('%[0-9]%', @p), 0, replicate('0', 5-charindex(' ', @p))) between FormattedFrom and FormattedTo

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 2

Nicely done, Ryan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Jeff Moden (5/12/2008)
Nicely done, Ryan.

Thanks Jeff 🙂

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

tina.muter Valued Member Points: 58 More actions · Answer 4

That looks good, I'll see if it fits with everything else.

Cheers. 😀