SP to find postcode, help please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SP to find postcode, help please

Author

Topic

swanagetown
Starting Member

19 Posts

Posted - 2005-05-11 : 10:06:25

Hi,

I'm stuggling with this stored proceedure, can't quiet get my head around it.

I've got a db with about 100,000 postcodes (rows) and need the sp to return either the exact post or nearest.

dbPostcodes(this is an example of the db column)
CM15
CM15 2
CM15 2EF
CM15 2AG
CM15 2SD
etc...

Stored Procedure
CREATE PROCEDURE dbo.getpostcode
(@mypostcodeStart char(10), @mypostcodeEnd char(10))
AS

SELECT Postcode,Coln1
FROM PostcodeTable
WHERE (Postcode Like @mypostcodeSart)
GO

Results I would like:

Say I passed a value of:
CM15 as the @mypostcodeStart &
2SF as the @mypostcodeEnd
I would want the SP to return CM15 2 as it's a closer match than CM15 but 2EF, 2AG, 2SD dont match as the whole of @mypostcodeEnd. However if CM15 2S had been it the db I would have wanted that.

Many thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-11 : 11:42:07

for first part use
SELECT Postcode,Coln1
FROM PostcodeTable
WHERE (Postcode Like @mypostcodeSart + '%')

the second one is a bit trickier because it'll need more processing of all possible combinations.
you could also use min or max function to get the nearest one.

Go with the flow & have fun! Else fight the flow

swanagetown
Starting Member

19 Posts

Posted - 2005-05-11 : 12:43:27

Thanks spirit

I'm thinking of using just one parameter for the whole codecode rather than the 2. As you can tell I'm not very familiar with sql... would something like this work?

CREATE PROCEDURE dbo.getpostcode
(@wholepostcode char(10))
AS

DECLARE @1st char(10)
SELECT @1st = @mypostcodeST

DECLARE @2nd char(10)
SELECT @2nd = left(@mypostcodeST, @mypostcodeST.length - 1)

DECLARE @3rd char(10)
SELECT @3rd = left(@mypostcodeST, @mypostcodeST.length - 2)

SELECT (CASE WHEN Postcode = @1st THEN Postcode WHEN Postcode = @2nd THEN Postcode WHEN Postcode = @3rd THEN Postcode END) AS Postcode, Coln1
FROM PostcodeTable
GO

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-11 : 13:30:17

you could try this...


declare @address table (postcode varchar(50))
insert into @address
select 'CM15' union all
select 'CM15 2' union all
select 'CM15 2EF' union all
select 'CM15 2AG' union all
select 'CM15 2SD'

declare @search varchar(20)
set @search = 'CM15'
declare @search1 varchar(20)
set @search1 = '2SF'

DECLARE @1st varchar(10)
SELECT @1st = @search1

DECLARE @2nd varchar(10)
SELECT @2nd = left(@search1, len(@search1) - 1)

DECLARE @3rd varchar(10)
SELECT @3rd = left(@search1, len(@search1) - 2)

select @1st, @2nd, @3rd

select 	* 
from 	@address
where	postcode like @search + '%' 
	and (postcode like '%' + @1st
	or postcode like '%' + @2nd
	or postcode like '%' + @3rd)

Go with the flow & have fun! Else fight the flow

swanagetown
Starting Member

19 Posts

Posted - 2005-05-11 : 15:06:03

Thanks I'll have a go with that. In the meantime I've got it down to:

CREATE PROCEDURE dbo.getpostcode
(@Wholepostcode char(10))
AS

DECLARE @1st char(10)
SELECT @1st = @Wholepostcode

DECLARE @2nd char(10)
SELECT @2nd = left(@1st, len(@1st) - 1)

DECLARE @3rd char(10)
SELECT @3rd = left(@2nd, len(@2nd) - 1)

SELECT Postcode, Coln1
FROM PostcodeTable
Where (Postcode = @1st) or (Postcode = @2nd) or (Postcode = @3rd)
GO

Only problem is it returns more than one row. I need it to return the longest option only

Hummmm

swanagetown
Starting Member

19 Posts

Posted - 2005-05-12 : 03:36:37

Hummm, still can't get this to work. Tried your suggestion Spirit but unfortunately no luck. I'm now down to the code below which works fine and only shows the row with the longest postcode However as soon as I add "coln1" to "SELECT MAX(Postcode)AS Postcode" i.e "SELECT MAX(Postcode) AS Postcode, coln1" then it stop working:

CREATE PROCEDURE dbo.getpostcode
(@Wholepostcode char(10))
AS

DECLARE @1st char(10)
SELECT @1st = left(@Wholepostcode, len(@Wholepostcode) - 1)

DECLARE @2nd char(10)
SELECT @2nd = left(@1st, len(@1st) - 1)

DECLARE @3rd char(10)
SELECT @3rd = left(@2nd, len(@2nd) - 1)

SELECT MAX(Postcode) AS Postcode
FROM PostcodeTable
WHERE (Postcode = @Wholepostcode) OR
(Postcode = @1st) OR
(Postcode = @2nd) OR
(Postcode = @3rd) OR

Please help

swanagetown
Starting Member

19 Posts

Posted - 2005-05-12 : 03:47:17

The only way I can think of getting this to work is to return "SELECT MAX(Postcode) AS Postcode" to my code behind file then execute a second sp getting all the details required using the results of the 1st sp. It works fine but there must be a getter way?

Thanks

swanagetown
Starting Member

19 Posts

Posted - 2005-05-12 : 05:33:38

Got there in the end using Embedded Select Statement

Where Postcode IN (SELECT Max(Postcode) FROM BCTable3 WHERE (Postcode = @wholepostcode) or (Postcode = @1st) or (Postcode = @2nd) or (Postcode = @3rd))

Must put my brain into gear!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources