SQL Server 2008 UK postcode text manipulation - Exact to district or suffix level

Question

I have some data like below:

W2 2JP 
W14 9NF 
W1K 5PE 
W1K 5PE 
SW7 5EU 
W1K 5PE 
N2 9QB

I would like to have output like:

W2
W14
W1
W1
SW7
W1
N2

Any help or direction appreciated, I can find and replace to where I get everthing to left of ' ' but I cannot then exclude the letter at the end of the first half of postcode

Gouri Shankar Aechoor · Answer 1 · 2014-07-29T17:41:52.090

1

You can sue SUBSTRING and CHARINDEX

WITH cte(UKPostCodes) AS
(
SELECT 'W2 2JP' UNION ALL
SELECT 'W14 9NF' UNION ALL
SELECT 'W1K 5PE' UNION ALL
SELECT 'W1K 5PE' UNION ALL
SELECT 'SW7 5EU' UNION ALL
SELECT 'W1K 5PE' UNION ALL
SELECT 'N2 9QB'
)
SELECT CASE WHEN SUBSTRING(SUBSTRING(UKPostCodes,1,CHARINDEX(' ',UKPostCodes)-1),3,1) LIKE '[a-zA-z]'
          THEN SUBSTRING(SUBSTRING(UKPostCodes,1,CHARINDEX(' ',UKPostCodes)-1),1,2)
          ELSE SUBSTRING(UKPostCodes,1,CHARINDEX(' ',UKPostCodes)-1)
      END
FROM cte

edited Jul 29 '14 at 17:41

answered Jul 29 '14 at 16:49

Gouri Shankar Aechoor

1,561
1
8
8

Thanks. I used SUBSTRING(q.PICKUP_POSTCODE,1,CHARINDEX(' ',q.PICKUP_POSTCODE)-1) and I still have W1K? (q.pickup_postcode is a column of uk postcodes) – finngeraghty Jul 29 '14 at 17:26
So what the logic for that. Replace all 'K' or remove 3rd character if not numeric. Please respond and I can help. – Gouri Shankar Aechoor Jul 29 '14 at 17:36
1

I have modified the query to remove the 3rd character if its not numeric (W1K -> W1, SLW - > SL). Please accept the answer if this works for you. – Gouri Shankar Aechoor Jul 29 '14 at 17:43

SQL Server 2008 UK postcode text manipulation - Exact to district or suffix level

1 Answers1