split UK Post Code

Question

I have a table which has Name and Address of all my clients, one of the column is Post Code (HA3 5NZ or W1E2 1RM or NW10 7LR). I need to print a report showing Post Code, First Part Post Code, Second Part Post Code:

Col1, Col2, Post Code, First Part Post Code, Second Part Post Code, Colx

Any help please

MP

Answer 1

Use the string functions described in Books Online http://msdn.microsoft.com/en-us/library/ms181984.aspx Here is one way. I used a variable because I don't have your table, but you should be able to swap your column names into this.

DECLARE @postcode varchar(10)
SET @postcode = 'W1E2 1RM'
SELECT @postcode AS [Post Code],
LEN(@postcode) AS [Length of the Post Code],
CHARINDEX (' ', @postcode) AS [location of the space],
LEFT(@postcode, CHARINDEX(' ', @postcode) - 1) AS[First Part Post Code],
RIGHT(@postcode, (LEN(@postcode) - (CHARINDEX(' ', @postcode))))
AS [Second Part Post Code];

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty