Retrieving data from a postcode string

• James Knight

  SSC Enthusiast

  Points: 145

  More actions

  March 9, 2005 at 6:39 am

  #546124

  I think one thing guaranteed if you want to get the 1st section of the postcode is that the last 3 digits always make up the 2nd section, therefore if you remove the last 3 characters and right trim it to remove a space (if it exists) then it should work:

  SELECT RTRIM(LEFT(@postcode, LEN(@postcode) - 3))

• Simon Martin

  SSC-Addicted

  Points: 468

  More actions

  March 9, 2005 at 6:42 am

  #546125

  Thanks James

  That's doing the job!

  ---

  Sharing knowledge saves valuable time!
  Simon Martin

• kpspencer

  Mr or Mrs. 500

  Points: 586

  More actions

  March 9, 2005 at 9:06 am

  #546168

  I have been working on a very similar problem with Postcodes!

  We have all sorts of problems with incomplete or incorrect postcodes but need to report on various postcode regions. the following is the sql for a view I created which looks at the postcodes and strips out the first part upto the space or returns just the first part if that is all that has been stored

  ie. w1 2ab woud return w1

  w1t 2ab would return w1

   

  Very Messy but it works for me.

   

  Karl

   

   

  SELECT     Client_Ref, Post_Code, PostCodeRegion = CASE WHEN Post_Code IS NULL

                        THEN 'PostCodeError' WHEN Post_Code = '' THEN 'PostCodeError' WHEN (Post_Code LIKE '%[`!"£$%^&*()_+#''*?_;,./]%')

                        THEN 'PostCodeError' WHEN substring(ltrim(Post_Code), 1, 1) NOT LIKE '[a-z]' THEN 'PostCodeError' WHEN substring(ltrim(Post_Code), 2, 1)

                        = '0' THEN 'PostCodeError' WHEN

                            (SELECT     CHARINDEX(' ', ltrim(Post_Code))) = 0 AND (len(ltrim(Post_Code))) < 5 THEN CASE WHEN isnumeric(SUBSTRING(ltrim(Post_Code),

                        Len(ltrim(Post_Code)), 1)) = 1 THEN ltrim(Post_Code) WHEN isnumeric(SUBSTRING(ltrim(Post_Code), Len(ltrim(Post_Code)), 1))

                        = 0 THEN CASE WHEN isnumeric(SUBSTRING(ltrim(Post_Code), Len(ltrim(Post_Code)) - 1, 1)) = 1 THEN SUBSTRING(ltrim(Post_Code), 1,

                        Len(ltrim(Post_Code)) - 1) ELSE 'PostCodeError' END END WHEN

                            (SELECT     CHARINDEX(' ', ltrim(Post_Code))) = 0 AND (len(ltrim(Post_Code))) > 4 THEN 'PostCodeError' WHEN

                            (SELECT     CHARINDEX(' ', ltrim(Post_Code))) > 0 AND

                            (SELECT     CHARINDEX(' ', ltrim(Post_Code))) < 6 THEN CASE WHEN isnumeric(SUBSTRING(ltrim(Post_Code), (CHARINDEX(' ', ltrim(Post_Code)) - 1),

                        1)) = 1 THEN SUBSTRING(ltrim(Post_Code), 1, (CHARINDEX(' ', ltrim(Post_Code)))) WHEN isnumeric(SUBSTRING(ltrim(Post_Code), (CHARINDEX(' ',

                        ltrim(Post_Code)) - 1), 1)) = 0 THEN CASE WHEN isnumeric(SUBSTRING(ltrim(Post_Code), (CHARINDEX(' ', ltrim(Post_Code)) - 2), 1))

                        = 0 THEN 'PostCodeError' ELSE SUBSTRING(ltrim(Post_Code), 1, (CHARINDEX(' ', ltrim(Post_Code)) - 2)) END END WHEN

                            (SELECT     CHARINDEX(' ', ltrim(Post_Code))) > 4 THEN 'PostCodeError' END

  FROM         Clients