Use Postcode Query Result As Join to Another Table

Transact-SQL
1

I have a requirement to retrieve a nominal ledger code based on the postcode sector of a delivery postcode in a contract. (The UK Postcode Sector is the single digit after the space, so for SL9 1DY, I need SL9 1). I have a query which extracts the postcode up to the sector from the delivery address on the contract. But I need to use this result to join to the table that holds the nominal code for that postcode sector and return the NLCC:

This is what I'm aiming for:

 +--------+----------+------+
| CONTNO | DELPCODE | NLCC |
+--------+----------+------+
| 300015 | SL9 0LY  |   17 |
| 300138 | SL6 1NF  |   14 |
| 300412 | SL6 5LL  |   14 |
| 300573 | SL6 2PL  |   14 |
| 300696 | SL0 9JQ  |   17 |
| 300764 | SL5 7EQ  |   14 |
+--------+----------+------+

The very simplified Contracts table can be built like this:

CREATE TABLE #CONTRACTS (CONTNO varchar(10), DELPCODE varchar(10));

INSERT INTO #Contracts (CONTNO, DELPCODE) VALUES ('300015','SL9 0LY');
INSERT INTO #Contracts (CONTNO, DELPCODE) VALUES ('300138','SL6 1NF');
INSERT INTO #Contracts (CONTNO, DELPCODE) VALUES ('300412','SL6 5LL');
INSERT INTO #Contracts (CONTNO, DELPCODE) VALUES ('300573','SL6 2PL');
INSERT INTO #Contracts (CONTNO, DELPCODE) VALUES ('300696','SL0 9JQ');
INSERT INTO #Contracts (CONTNO, DELPCODE) VALUES ('300764','SL5 7EQ');

This script creates the DEPOTPCAREA table where the nominal code must be looked up from:

CREATE TABLE #DEPOTPCAREA (PCDSECTOR nvarchar(6), NLCC varchar(8));

INSERT INTO #DEPOTPCAREA (PCDSECTOR, NLCC) VALUES ('SL9 0','17');
INSERT INTO #DEPOTPCAREA (PCDSECTOR, NLCC) VALUES ('SL6 1','14');
INSERT INTO #DEPOTPCAREA (PCDSECTOR, NLCC) VALUES ('SL6 5','14');
INSERT INTO #DEPOTPCAREA (PCDSECTOR, NLCC) VALUES ('SL6 2','14');
INSERT INTO #DEPOTPCAREA (PCDSECTOR, NLCC) VALUES ('SL0 9','17');
INSERT INTO #DEPOTPCAREA (PCDSECTOR, NLCC) VALUES ('SL5 7','14');

The code below on it's own gets me the postcode sector from a given postcode correctly, I just can't work out how to use it in the join to the DEPOTPCAREA table in order to look up and retrieve the NLCC:

DECLARE @postcode VARCHAR(20)

SET @postcode = 'SL9 0LY'

SELECT SUBSTRING(postcode, start, length - start - fromEnd) AS PCDSECTOR
FROM (
	SELECT MAX(PATINDEX([matched], @postcode)) AS start
		,MAX(PATINDEX([ReverseMatch], reverse(@postcode + ' '))) - 1 AS fromEnd
		,len(@postcode + '||') AS [length]
		,@postcode AS postcode
	FROM (
		SELECT '[A-Z][A-Z0-9] [0-9]%'
			,'%[0-9] [A-Z0-9][A-Z]'
		
		UNION
		
		SELECT '[A-Z][A-Z0-9][A-Z0-9] [0-9]%'
			,'%[0-9] [A-Z0-9][A-Z0-9][A-Z]'
		
		UNION
		
		SELECT '[A-Z][A-Z0-9][A-Z0-9][A-Z0-9] [0-9]%'
			,'%[0-9] [A-Z0-9][A-Z0-9][A-Z0-9][A-Z]'
		) a(MATCHED, ReverseMatch)
	) pc

Result: SL9 0 = correct, but I need to use this to bring back the NLCC value of 17 from the DEPOTPCAREA table

Any help would be appreciated.

2

hi

i tried to do this ... hope it helps :slight_smile: :slight_smile:
i love any feedback

if i am missing anything please point it out

i did not understand your reverse match etc
i did it in my own way of thinking !!!

drop create data 
DROP TABLE #CONTRACTS
GO 

CREATE TABLE #CONTRACTS (CONTNO varchar(10), DELPCODE varchar(10));

INSERT INTO #Contracts (CONTNO, DELPCODE) VALUES ('300015','SL9 0LY');
INSERT INTO #Contracts (CONTNO, DELPCODE) VALUES ('300138','SL6 1NF');
INSERT INTO #Contracts (CONTNO, DELPCODE) VALUES ('300412','SL6 5LL');
INSERT INTO #Contracts (CONTNO, DELPCODE) VALUES ('300573','SL6 2PL');
INSERT INTO #Contracts (CONTNO, DELPCODE) VALUES ('300696','SL0 9JQ');
INSERT INTO #Contracts (CONTNO, DELPCODE) VALUES ('300764','SL5 7EQ')
GO 

select * from #CONTRACTS 
go 

DROP TABLE #DEPOTPCAREA
GO 

CREATE TABLE #DEPOTPCAREA (PCDSECTOR nvarchar(6), NLCC varchar(8));
GO

INSERT INTO #DEPOTPCAREA (PCDSECTOR, NLCC) VALUES ('SL9 0','17');
INSERT INTO #DEPOTPCAREA (PCDSECTOR, NLCC) VALUES ('SL6 1','14');
INSERT INTO #DEPOTPCAREA (PCDSECTOR, NLCC) VALUES ('SL6 5','14');
INSERT INTO #DEPOTPCAREA (PCDSECTOR, NLCC) VALUES ('SL6 2','14');
INSERT INTO #DEPOTPCAREA (PCDSECTOR, NLCC) VALUES ('SL0 9','17');
INSERT INTO #DEPOTPCAREA (PCDSECTOR, NLCC) VALUES ('SL5 7','14');
GO

select * from #DEPOTPCAREA 
go
SQL ... 
DECLARE @postcode VARCHAR(20) 

SET @postcode = 'SL9 0LY' 

SELECT * 
FROM   #contracts a 
       JOIN #depotpcarea b 
         ON LEFT(a.delpcode, 5) = b.pcdsector 
WHERE  a.delpcode LIKE '%' + @postcode + '%'

image
image.png539×613 9.26 KB

3

Hi

I did have a go at this but there is a problem in that the first part of the UK postcode can vary in length from 2 to 4 characters. So, E1 1AA, EN1 1AA and EN11 1AA are various possibiliities. I also thought about getting everything upto 1 character after the space. But what if there is no space (somebody input it incorrectly in the data)? A lot of the time, this gets the right result:

DECLARE @DELPCODE as varchar(8)

SET @DELPCODE = 'EN11 1AA'

SELECT LEFT(@DELPCODE, CHARINDEX(' ', @DELPCODE) +1) PCDSECTOR

But of course, it doesn't help for instances where the postcode is in the incorrect format or is missing a space entirely.

I found looking at your join example helpful though, so thanks.

Martyn

5

But what if there is no space (somebody input it incorrectly in the data)? A lot of the time, this gets the right result:

replace all the spaces ...with empty strings... then take the first 5 characters and compare

hope this helps

DECLARE @string VARCHAR(100) = 'EN11 1AA'
DECLARE @string1 VARCHAR(100) = 'EN1 1AA'
DECLARE @string2 VARCHAR(100) = 'E1 1AA'

SELECT 'string', LEFT(Replace(@string, ' ', ''), 5)

SELECT 'string1', LEFT(Replace(@string1, ' ', ''), 5)

SELECT 'string2', LEFT(Replace(@string2, ' ', ''), 5)

image

1 Like
6

Excellent, thank you!

Martyn